Re: select and update by row number

logicprobe_at_bbs.kis.net (Steve Crowe) wrote:
>Hello,
>
>I am taking a CMIS course on databases. I am tring to update and query
>my database by row number. I have a table with 12,410 records in it. I
>need to query the 1st, the 6,205th, and the 12,410th rows. I tried
>using RowNum in a where clause, but no dice. I also am thinking about
>using RowID, but I am not sure how to go about this.
>
>Thanks for any help,
>Steve Crowe
>logicprobe_at_bbs.kis.net
>
Hi there,

You can't use rownum = 6205 or 12410. Rownum works only on the following syntax

where rownum =1 (or) where rownum < 10

In the first case, it will retrieve the first row (not necessarily the first row in the table. The row which optimizer retrieves first).In the second case, it will retrieve any 9 rows in a table.

Using rowid has got the same effect. Rowid's are not necessarily stored in order. The only way I can think of, is to find out the row which you need to update through primary key / unique key.

If your table does not have primary / unique key, retrieve the rowid

select rowid
from table1
where col1 = 'a'
and col2 ='b' ,

after the above step, update the table with rowid,

update table1
set col1 = 'new value'
where rowid = rowid retrieved from the above step

Hope the above helps

N.Prabhakar Received on Sat Nov 30 1996 - 00:00:00 CET