Oracle: Merge Date between tales OR copy from table to table

Ref:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5646797700346972949

You Asked

Hello,

I have a requirement to update 2 columns (A, B) in a arbitrarily (very, very) large table. The values that needs to be updated relies on values from another table and must be consistent with the values from the other table. Since the values in A differ from B, currently, i have to do 2 updates to the table for each record in the cursor. This is obviously inefficient. It can take longer than 12 hours to complete (and has been confirmed as such). There surely must be some way to improve the process. All my reading and trawling the web seems to point to using FETCH, BULK COLLECT and LIMIT etc. Is this the way to go? Can you please perhaps furnish an example?

See the code example below:

declare
CURSOR tran
IS
  SELECT oid, user_account_number FROM user_account001;
BEGIN
  FOR t IN tran
  LOOP
    UPDATE transaction001
    SET payer_account_number = t.user_account_number
    WHERE payer_account_oid  = t.oid;
    UPDATE transaction001
    SET payee_account_number = t.user_account_number
    WHERE payee_account_oid  = t.oid;
  END LOOP;
END;

Thanks!

and we said...

why is this anything more than:

begin

merge into transaction001 t
using user_account001 ua
on ( t.payer_account_oid = ua.oid )
when matched then update set payer_account_number = ua.user_account_number;

merge into transaction001 t
using user_account001 ua
on ( t.payee_account_oid = ua.oid )
when matched then update set payee_account_number = ua.user_account_number;

end;