Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Using UPDATE with ROWNUM?
Hi,
I have a production tablespace with millions of rows.
I need to set sequence values in each row but I can not lock the table
for any more than 5 seconds.
I created SEQUENCE named tempseq and found that I can update 1000 rows
in a short enough time.
Here is the script I tested. I have tested it but I don't understand how each row (from the SELECT) is getting processed only once, or if this script even guarantees that this will happen.
If someone can explain how ROWNUM is working in this UPDATE then that
would help me.
Alternative solutions (that don't involve explicit cursors) are also
welcome.
begin
loop update (select seq_no from mytable) set seq_no = tempseq.nextval where rownum <= 1000; exit when SQL%FOUND = false; commit; end loop;
TIA
-Darren
Received on Tue Jun 26 2007 - 10:54:01 CDT