Update based on Row id

[Revanth reddy]

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.

[Paul Campbell]

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]

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]

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]

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]

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]

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