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:
Partner
or Supplier
,eg: in one table is it Partner
, in antoher table is it Supplier
)Partner
or Supplier
) but only in 1 table, depends on PKYou 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;
/