I have a problem where I need to run a set of update statements based on column values.
So we will get a set of IDs like below after a select query.
------------------------------------- ID ChangedColumns ChangedValues ------------------------------------- 101 col1|col2| [A^B]|[123^456]| 102 col3| [XXX^YYY]| ... .... .... and so on
Where ChangedColumns are the column names which are changed and ChangedValues will tell the old and new values for the respective columns (delimited by a pipe ‘|’ and enclosed in braces ‘[]’)
Based on this, I need to run update statements on another table, which will be something like
update table set col1='A', col2='123' where id = 101; update table set col3='XXX' where id = 102; update table ..... ..... and so on
The number of Changed Columns can be 1 – 20 and its corresponding changed values will be in same order in ChangedValues column.
Could someone please let me know how to write a PL/SQL block for the same?
Many thanks
Advertisement
Answer
First I broke the data down by ID and ChangedColumn:
SQL> with tbl(ID, ChangedColumns, ChangedValues) as ( select 101, 'col1|col2|', '[A^B]|[123^456]|' from dual union select 102, 'col3|', '[XXX^YYY]|' from dual ) SELECT ID, REGEXP_SUBSTR(ChangedColumns ,'([^|]*)(|)', 1, COLUMN_VALUE, NULL, 1 ) as changedcolumn, REGEXP_SUBSTR(ChangedValues ,'([^|]*)(|)', 1, COLUMN_VALUE, NULL, 1 ) as column_value FROM tbl, TABLE( CAST( MULTISET( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT( ChangedColumns ,'|' ) ) AS SYS.ODCINUMBERLIST ) ); ID CHANGEDCOL COLUMN_VALUE ---------- ---------- ---------------- 101 col1 [A^B] 101 col2 [123^456] 102 col3 [XXX^YYY] SQL>
Then changed that to generate a separate update statement for each column that changed for each ID. The next step is to work it into one update statement for each ID, handling all columns that changed:
SQL> with tbl(ID, ChangedColumns, ChangedValues) as ( select 101, 'col1|col2|', '[A^B]|[123^456]|' from dual union select 102, 'col3|', '[XXX^YYY]|' from dual ) SELECT 'update table set ' || REGEXP_SUBSTR(ChangedColumns, '([^|]*)(|)', 1, COLUMN_VALUE, NULL, 1 ) || ' = ''' || REGEXP_SUBSTR(ChangedValues, '[([^^]*)(^)', 1, COLUMN_VALUE, NULL, 1 ) || '''' || ' where id = ' || id || ';' as update_stmt FROM tbl, TABLE( CAST( MULTISET( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT( ChangedColumns ,'|' ) ) AS SYS.ODCINUMBERLIST ) ); UPDATE_STMT -------------------------------------------------------------------------------- update table set col1 = 'A' where id = 101; update table set col2 = '123' where id = 101; update table set col3 = 'XXX' where id = 102; SQL>
EDIT: Here’s the final product after some further refining and tweaking:
SQL> with tbl_a(ID, ChangedColumns, ChangedValues) as ( select 101, 'col1|col2|', '[A^B]|[123^456]|' from dual union select 102, 'col3|', '[XXX^YYY]|' from dual union select 103, 'col4|col5|col6|col7|col8|', '[XX0^YY0]|[XX1^YY1]|[XX2^YY2]|[XX3^YY3]|[XX4^YY4]|' from dual ), tbl_b(ID, column_value) as ( SELECT ID, REGEXP_SUBSTR(ChangedColumns ,'([^|]*)(|)', 1, COLUMN_VALUE, NULL, 1 ) || ' = ' || CHR(39) || REGEXP_SUBSTR(ChangedValues ,'[([^^]*)(^)', 1, COLUMN_VALUE, NULL, 1 ) || CHR(39) FROM tbl_a, TABLE( CAST( MULTISET( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT( ChangedColumns ,'|' ) ) AS SYS.ODCINUMBERLIST ) ) ) SELECT distinct ID, 'UPDATE TABLE SET ' || LISTAGG(column_value, ', ') WITHIN GROUP (ORDER BY id, column_value) OVER (PARTITION BY id) || ' WHERE ID = ' || ID || ';' as update_stmt FROM tbl_b ORDER BY ID; ID UPDATE_STMT --------- ------------------------------------------------------------------------------------------------------ 101 UPDATE TABLE SET col1 = 'A', col2 = '123' WHERE ID = 101; 102 UPDATE TABLE SET col3 = 'XXX' WHERE ID = 102; 103 UPDATE TABLE SET col4 = 'XX0', col5 = 'XX1', col6 = 'XX2', col7 = 'XX3', col8 = 'XX4' WHERE ID = 103; SQL>
I suspect this could be further simplified, but there’s something to be said for breaking a problem down into smaller steps.