PL/SQL Update row value via cursor

Question

I am new to pl/sql and I got stuck. I have a table consisting of 3 columns: package_uid, csms_package_id and flag. First two columns are filled and the third is empty. Flag column is filled when a procedure is called in a way that procedure compares package_id-s from that table and another table and if they are a match, the flag should be 'YES'. This is my code:

DECLARE
  package_uid varchar2(256);
  CURSOR abc IS SELECT csms_package_id, PACKAGE_UID, FLAG FROM table1 tpc;
  CURSOR defg IS SELECT package_id FROM table2 tpc2;
BEGIN
  for i in abc  loop
    for j in defg loop
      begin
        if i.CSMS_PACKAGE_ID=j.package_id THEN
          i.flag := 'YES' ;
          DBMS_OUTPUT.PUT_LINE(i.flag);
        end if;
      end;
    end loop;
  end loop;

end;

The output writes 'yes' correct number of times but my table is not updated with 'yes' values. How can I update flag values in the table?

Littlefoot · Accepted Answer · 2021-06-02 08:11:47Z

3

You aren't updating anything; if you want to do that, you'll have to use UPDATE or MERGE statement.

Though, why PL/SQL and why nested loops? That looks highly inefficient. How about a simple & single merge instead?

MERGE INTO table1 a
     USING table2 b
        ON (a.package_uid = b.package_id)
WHEN MATCHED
THEN
   UPDATE SET a.flag = 'YES';

answered Jun 2, 2021 at 8:11

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

1 Comment

OmarLittle Over a year ago

Thanks pal! Why nested loops? Bcz I'm a newbie. Thanks for your help, very much appreciated!

Collectives™ on Stack Overflow

PL/SQL Update row value via cursor

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