Re: select and update by row number

: >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.
 

: 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 I understand the question you are trying to update the 1st, 6205th and 12410 rows. If so, read on.

As hinted at by the previous post if there is a unique key that identifies the row number in some way then use that, otherwise you could use the fact that an un-constrained SELECT (ie no WHERE or ORDER BY clause) will retrieve the rows in the order that they are stored in the database.

Thus, how about some code that simply reads in the rowid for every record and then uses a modulus/count operation to determine if a row is to be updated and if so, it updates using the rowid.

The code could be written in PL/SQL, Oraperl or a Pro* language with minimal effort.

PL/SQL pseudo-code:

CURSOR FOR (SELECT rownum, rowid FROM table) LOOP
    IF (rownum is a desired record number)     THEN
        UPDATE table WHERE rowid = rowid;     END IF;
END LOOP; I'll leave the student to write the code and to think about any locking strategies that may be required.

Please credit the newsgroup in the homework :)

IAP Received on Sun Dec 01 1996 - 00:00:00 CET