Re: Using UPDATE with ROWNUM?

On Jun 26, 2:57 pm, "Chris L." <diver..._at_uol.com.ar> wrote:
> You don't need to bulk collect from the sequence. I suggest you bulk
> collect the primary key of the table being updated.
>
> Then, using FORALL, you update the records with the sequence's
> nextval.
>
> As follows (example modified from D. Morgan's page)
>
> CREATE TABLE parent (part_num NUMBER, part_name VARCHAR2(10),seq_no
> number);
> ALTER TABLE parent ADD PRIMARY KEY (part_num,part_name);
>
> INSERT INTO parent (part_num,part_name)
> SELECT 1,'a' FROM dual UNION
> SELECT 2,'b' FROM dual UNION
> SELECT 3,'c' FROM dual UNION
> SELECT 4,'d' FROM dual ;
>
> declare
> TYPE array_one IS TABLE OF parent.part_num%TYPE;
> TYPE array_two IS TABLE OF parent.part_name%TYPE;
> tbl_num array_one;
> tbl_name array_two;
>
> CURSOR r IS
> SELECT part_num,part_name
> FROM parent;
>
> BEGIN
> OPEN r;
> LOOP
> FETCH r BULK COLLECT INTO tbl_num,tbl_name LIMIT 1000;
>
> FORALL i IN 1..tbl_num.COUNT
> UPDATE parent SET seq_no=tempseq.NEXTVAL
> WHERE part_num=tbl_num(i)
> AND part_name=tbl_name(i);
>
> EXIT WHEN r%NOTFOUND;
> END LOOP;
> COMMIT;
> CLOSE r;
> END ;
> /

This works great. Much faster than my original script. Thanks.

> (remember, I didn't know about bulk collect three days ago, so you
> might want to look it up some more in the manual)
This example is as good as any from the manual. Received on Tue Jun 26 2007 - 22:19:52 CDT