Dynamic sql - update multiple rows with loop

Question

We have a lot of tables which contain 3 cols (RID,PARTNER,SUPPLIER). I want to update theese cols, but only in 4 tables - depends on PK.

so i started something, but i stuck:

BEGIN
 FOR i IN
  (select table_name from all_tab_columns where column_name = 'RID' /*PK*/ 
   and column_name = 'PARTNER' or column_name = 'SUPPLIER')
 LOOP
  EXECUTE IMMEDIATE 'UPDATE  ' || i.table_name|| 'set PARTNER = :newvalue where PARTNER = :oldavalue and RID = :ridvalue'
  USING (newvalue, oldvalue, ridvalue)
END LOOP 
END

Problems:

I dont know, how can i update multiple cols with this method (because in tables exists Partner or Supplier,eg: in one table is it Partner, in antoher table is it Supplier)
I want to update only (Partner or Supplier) but only in 1 table, depends on PK
Is this a workable solution ?

Popeye · Accepted Answer · 2020-04-30 12:15:07Z

1

You need to use conditional USING clause and little bit different query in FOR loop as follows:

BEGIN
    FOR I IN (
        SELECT TABLE_NAME
               , PARTNER_COUNT
               , SUPPLIER_COUNT
        FROM (
            SELECT TABLE_NAME
                   , SUM(CASE WHEN COLUMN_NAME = 'RID' THEN 1 ELSE 0 END) AS RID_COUNT
                   , SUM(CASE WHEN COLUMN_NAME = 'PARTNER' THEN 1 ELSE 0 END) AS PARTNER_COUNT
                   , SUM(CASE WHEN COLUMN_NAME = 'SUPPLIER' THEN 1  ELSE 0 END) AS SUPPLIER_COUNT
            FROM ALL_TAB_COLUMNS
            WHERE COLUMN_NAME = 'RID'
                  OR COLUMN_NAME = 'PARTNER'
                  OR COLUMN_NAME = 'SUPPLIER'
        ) WHERE RID_COUNT = 1 AND PARTNER_COUNT + SUPPLIER_COUNT = 1
    ) LOOP
        EXECUTE IMMEDIATE 'UPDATE  '
                          || I.TABLE_NAME
                          || 'set :col_name = :newvalue where :col_name = :oldavalue and RID = :ridvalue'
            USING ( DECODE(PARTNER_COUNT,1,'PARTNER','SUPPLIER'),
                    NEWVALUE, 
                    DECODE(PARTNER_COUNT,1,'PARTNER','SUPPLIER'),
                    OLDVALUE, 
                    RIDVALUE );
    END LOOP;
END;
/

answered Apr 30, 2020 at 12:15

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

1 Comment

Georgy Over a year ago

Exactly what i wanted. Awesome.

Collectives™ on Stack Overflow

Dynamic sql - update multiple rows with loop

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