How to pick only a particular set of values from a column and generate an update statement based on that

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

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.