Oracle For Loop to Update Records in a table

Question

I have two tables, doc (in a database1) and docs (in a database2). With the below key fields:

doc: KEY,OLD_KEY
docs: RKEY

Now doc.OLD_KEY = docs.RKEY. I am working on checking where doc.OLD_KEY = docs.RKEY and set docs.RKEY = doc.KEY.

I have written the below syntax, however it i am not being able to successfully run for i haven't worked with for loop before.

DECLARE
  OLD_KEY_var  NUMBER(10);
  KEY_var  NUMBER(38);
BEGIN
FOR dc.KEY IN (
  SELECT dc.KEY,dc.OLD_KEY INTO KEY_var,OLD_KEY_var
  FROM user.database2 docs,database1 dc
  where docs.RKEY=dc.OLD_KEY
  ) LOOP
   UPDATE user.database2 docs SET docs.RKEY = KEY_var where docs.RKEY=OLD_KEY_var;
 END LOOP;
END;

I have a connexion and grant priviliges between the tables, and i am getting

Why loop at all? why not just update using a correlated update or a merge. https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table I've yet to encounter a situation where set based processing is slower than a loop I code. — xQbert, May 18 '18 at 15:29

score 0 · Answer 1 · answered May 18 '18 at 15:35

You do not need variables for this operation, You can do this as follows:

BEGIN
  FOR dc IN (SELECT dc.KEY, dc.OLD_KEY
               FROM user.database2 docs, database1 dc
              where docs.RKEY = dc.OLD_KEY) LOOP
    UPDATE user.database2 docs
       SET docs.RKEY = dc.KEY
     where docs.RKEY = dc.OLD_KEY;
  END LOOP;
END;

But the option offered by xQbert seemed a lot better!

score 0 · Answer 2 · answered May 18 '18 at 15:40

0

No loops or PL/SQL block is required. A single update like this will do.

 UPDATE user.database2  
                 docs SET 
                   docs.RKEY = ( select KEY FROM database1 dc 
                       where 
                             docs.RKEY=dc.OLD_KEY
                             );

This assumes that only 1 row is returned per key, otherwise you must select MIN(key) or MAX(key) whichever suits your data, in the inner select

answered May 18 '18 at 15:40

Kaushik Nayak

30,772
5
32
45

You have a point, however select KEY FROM database1 dc where docs.RKEY=dc.OLD_KEY is returning multiple records. I am not sure how MIN(key) or MAX(key) would solve the problem? – JDOE May 19 '18 at 09:26
@JDOE : Edit your question and add some sample data from both tables, showing duplicates which you are talking about and the expected result you want to see in the table post the update. – Kaushik Nayak May 19 '18 at 09:33

Oracle For Loop to Update Records in a table

2 Answers2