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.

CodePudding user response：

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
               );