Update using for loop in plsql

Question

I have trouble updating my table. My script works, but in the end, my table is not updated.

    DECLARE
      RCT_ID VARCHAR(6);
      CURSOR CUR IS
        SELECT T.PRM FROM TABLE_NAME T;
    
    BEGIN
    
      FOR MY_CUR IN CUR LOOP
      
        SELECT ID
          INTO RCT_ID
          FROM njn_refcpt r
         WHERE r.num_pce_pdl = my_cur.prm;               
       
    dbms_output.put_line('RCT_ID: ' || RCT_ID); 
    dbms_output.put_line('my_cur.prm: ' || my_cur.prm);

    UPDATE TABLE_NAME t SET T.RCT_ID = RCT_ID WHERE t.prm = my_cur.prm;
    COMMIT;
    
    END LOOP;
    
    END;

The output displays values for RCT_ID and my_cur.prm. So why doesn't the update work? Thanks.

Littlefoot · Accepted Answer · 2022-12-13 07:56:07Z

4

That's because of

UPDATE TABLE_NAME t SET T.RCT_ID = RCT_ID WHERE t.prm = my_cur.prm;
                          ------   ----
                          these two are just the same

To Oracle, it means that you want to set TABLE_NAME.RCT_ID to its own value, so you're basically updating it to what it was instead of a value fetched from NJN_REFCPT table.

To avoid it, never name local variables (or parameters) as column names; use a prefix, e.g.

DECLARE
  L_RCT_ID njn_refcpt.id%type;      --> this
BEGIN

  SELECT ID
      INTO l_rct_id                 --> this
      FROM njn_refcpt r ...
...

On the other hand, you don't need slow row-by-row processing in a loop in PL/SQL - use an ordinary update:

update table_name t set
  t.rct_id = (select r.id
              from njn_refcpt r
              where r.num_pce_pdl = t.prm
             )
  where exists (select null
                from njn_refcpt a
                where a.num_pce_pdl = t.prm
               );

answered Dec 13, 2022 at 7:56

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lea Over a year ago

Thank you for the accurate answer and your help :)

Collectives™ on Stack Overflow

Update using for loop in plsql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related