Re: Using UPDATE with ROWNUM?

On 26 jun, 12:54, darr..._at_yahoo.com wrote:
> 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;
> end;
>
> TIA
> -Darren

You need to include some condition to assure the update affects "unupdated"  rows only.

as in

update (etc)
set seq_no = (etc) where rownum <= 1000
and seq_no is null;

This way the next update will affect the next 1000 rows and so on.

But make sure to look up "bulk collect" and "forall" updating examples, which D. Morgan of this same newsgroup recently pointed me to! Much higher performance. Received on Tue Jun 26 2007 - 11:27:26 CDT