• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Update based on Row id

 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Guys,
I have a doubt in updating the table based on row id.
I have a table which doen't have any constaints( no primary key).my requirement is in the input screen in jsp we will provide the textbox to user, he enters some kit number. then i am displaying all the rows with that kit number in the next sreen. and also making the kit as button in the grid, my question is how do we update only clicked kit based on row id. how do we send this row id to the db.
any help is greatly appreciated.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by kumar kosaraju:
Hi Guys,
I have a doubt in updating the table based on row id.
I have a table which doen't have any constaints( no primary key).my requirement is in the input screen in jsp we will provide the textbox to user, he enters some kit number. then i am displaying all the rows with that kit number in the next sreen. and also making the kit as button in the grid, my question is how do we update only clicked kit based on row id. how do we send this row id to the db.
any help is greatly appreciated.



A rowid is a pseudo-column that represents a row's physical location in a table (that is an important thing to remember). Since it does represent a row's physical location in table... this location can change when a row is inserted or deleted prior to your row. You should not use rowid to update your table unless you know it is not possible to insert/delete rows from the table while it is in use. A rowid, even though it is a unique key, it shouldn't be used as your table's primary key.

It is however, the fastest way to select (read) a row from your table in a query.

You should be using the same keys that you use to uniquely identify the row in your update.
 
Revanth reddy
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Paul Campbell:


A rowid is a pseudo-column that represents a row's physical location in a table (that is an important thing to remember). Since it does represent a row's physical location in table... this location can change when a row is inserted or deleted prior to your row. You should not use rowid to update your table unless you know it is not possible to insert/delete rows from the table while it is in use. A rowid, even though it is a unique key, it shouldn't be used as your table's primary key.

It is however, the fastest way to select (read) a row from your table in a query.

You should be using the same keys that you use to uniquely identify the row in your update.





Thaks for the reply
paul; but what i wanted to do is get the rowid when i am ready to update, and get the correponding existing record and update the record. my question is how do we tell the db which row id we are about to update ?

hope it is clear ?

-Kumar
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by kumar kosaraju:




Thaks for the reply
paul; but what i wanted to do is get the rowid when i am ready to update, and get the correponding existing record and update the record. my question is how do we tell the db which row id we are about to update ?

hope it is clear ?

-Kumar



Kumar,

You shouldn't use rowid for an update... rowid represents the row's physical position in the table... this position is transient and can change and cause you to update your table incorrectly.

However, if you're insistent on doing this... you will have to do it by executing two SQL statements for each record you want to update... the first selects your rowid into a variable... the second would be to update row associated with the rowid = your rowid variable.

It would be more efficient to do single Update query.

Is your concern that you are updating a table with no natural primary key and do not feel a row can be uniquely identified for an update?
[ December 19, 2008: Message edited by: Paul Campbell ]
 
Revanth reddy
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Paul Campbell:


Is your concern that you are updating a table with no natural primary key and do not feel a row can be uniquely identified for an update?

[ December 19, 2008: Message edited by: Paul Campbell ]




Paul,
Thankyou so much for your clarification. i want to do the same thing as you suggested, with 2 queries.
my requirement is i have a some kit number in db, with multiple records for the kitnumber. in the user enters the kitnumber then we will display all the rows for that kitnumber.

what i am doing now is getting all the rows for that kit number and displaying them in a grid. below each row i am also printitng the existing values in the textbox (for each row in the grid i am displaying text boxes with the existing values) so the user can updated any of the textboxes provided below each row and hiot update we hae to update the db with new values. i am done with 50%. i am able to pull all the rows based on user input and displying the data in the text boxes. my concern is how to retrive the values from text boxes and how do we tell the db about these values how do we send the rowid to db..

i know it might be confusing.. hope you understand it clearly...

Thanks
Kumar
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by kumar kosaraju:



Paul,
Thankyou so much for your clarification. i want to do the same thing as you suggested, with 2 queries.
my requirement is i have a some kit number in db, with multiple records for the kitnumber. in the user enters the kitnumber then we will display all the rows for that kitnumber.

what i am doing now is getting all the rows for that kit number and displaying them in a grid. below each row i am also printitng the existing values in the textbox (for each row in the grid i am displaying text boxes with the existing values) so the user can updated any of the textboxes provided below each row and hiot update we hae to update the db with new values. i am done with 50%. i am able to pull all the rows based on user input and displying the data in the text boxes. my concern is how to retrive the values from text boxes and how do we tell the db about these values how do we send the rowid to db..

i know it might be confusing.. hope you understand it clearly...

Thanks
Kumar



Actually not that confusing... but I am curious why you wouldn't use a surrogate key for your rows (unique key based on a sequence) and retrieve your rows where kit nbr = your kit nbr then for your update update the row with the corresponding sequential key (seq number)... it is conceptually similar to using rowid with the exception it is a physical value that isn't going to change... but if you're going with rowid, just include rowid in your select and store its value for each row.

select rowid, column1, column2, ...

then for your update use rowid = your stored rowid in the where clause.

update table
set column1 = var1
where rowid = myrowID;

If this doesn't make sense or you have questions... I'm snowed in today... so I'll be checking back fairly often.

Paul
 
Revanth reddy
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Paul Campbell:



select rowid, column1, column2, ...

then for your update use rowid = your stored rowid in the where clause.

update table
set column1 = var1
where rowid = myrowID;

If this doesn't make sense or you have questions... I'm snowed in today... so I'll be checking back fairly often.

Paul





Sounds good!!! Thanks Paul
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic