Re: Need advise from PL/SQL experts on script

From: Jeremy Ovenden <jovenden_at_hazelweb.co.uk>
Date: Fri, 16 Feb 2001 14:49:51 -0000
Message-ID: <t8qfbpma1rn8e5@xo.supernews.co.uk>

"LGold" <leighlhg_at_home.com> wrote in message news:y5oi6.3141$RU.116774_at_news1.rdc1.ab.home.com...
> Hi there,
>
> I need some advise from any PL/SQL expert regarding the PL/SQL script
 below.
> What I am trying to accomplish is to change customer number in a table to
 a
> new number. This new number is stored in a mapping table that consists of
 2
> columns, one stores the old customer, and the other, the new customer
> number. I am new in PL/SQL and would like comments on the script below. My
> concern is if I have a mapping table with data like this (see below), will
> the conversion be done twice to the row that has old customer number of
 333?
> If it has been converted to 456, will it run the risk of being converted
 to
> 777 after that?
>
> map table structure - old_cust, new_cust
> 333 456
> 444 457
> 456 777
>
> DECLARE
> cursor c1 is select a.rowid, b.new_cust from customer a, map_table b,
> where b.old_cust = a.cust_num;
> c1_rec c1%ROWTYPE;
> commit_cnt number := 0;
>
> BEGIN
> open c1;
> LOOP
> fetch c1 into c1_rec;
> exit when c1%NOTFOUND;
> update customer set cust_num = c1_rec.new_cust
> where rowid = c1_rec.rowid;
>
> commit_cnt := commit_cnt + 1;
> if commit_cnt > 20000 then
> commit;
> commit_cnt := 0;
> close c1;
> open c1;
> end if;
> END LOOP;
> close c1;
> commit;
> END;
> /
>
> Thanks.
>
>
>

Isn't it simpler to say or am I missing a subtlety here?

begin
  update customer a

  set cust_num = (select new_cust
                  from   map_table
                  where  old_cust = a.cust_num);

end;
/

jeremy Received on Fri Feb 16 2001 - 08:49:51 CST