I (using Oracle 12c, PL/SQL) need to update an existing table TABLE1 based on information stored in a table MAP. In a simplified version, MAP looks like this:

COLUMN1 to COLUMNn are column names in TABLE1 (but there are more columns, not just these). Now I need to update a column in TABLE1 if MODIFY in table MAP contains a 'Y' for that columns' name. There are other row conditions, so what I would need would be UPDATE statements of the form

UPDATE TABLE1  
SET COLUMNi = value_i  
WHERE OTHER_COLUMN = 'xyz_i';

where COLUMNi runs through all the columns of TABLE1 which are marked with MODIFY = 'Y' in MAP. value_i and xyz_i also depend on information stored in MAP (not displayed in the example).

The table MAP is not static but changes, so I do not know in advance which columns to update. What I did so far is to generate the UPDATE-statements I need in a query from MAP, i.e.

SELECT <Text of UPDATE-STATEMENT using row information from MAP> AS SQL_STMT  
FROM MAP  
WHERE MODIFY = 'Y';

Now I would like to execute these statements (possibly hundreds of rows). Of course I could just copy the contents of the query into code and execute, but is there a way to do this automatically, e.g. using EXECUTE IMMEDIATE? It could be something like

BEGIN  
EXECUTE IMMEDIATE SQL_STMT USING 'xyz_i'; 
END;

only that SQL_STMT should run through all the rows of the previous query (and 'xyz_i' varies with the row as well). Any hints how to achieve this or how one should approach the task in general?

EDIT: As response to the comments, a bit more background how this problem emerges. I receive an empty n x m Matrix (empty except row and column names, think of them as first row and first column) quarterly and need to populate the empty fields from another process.

The structure of the initial matrix changes, i.e. there may be new/deleted columns/rows and existing columns/rows may change their position in the matrix. What I need to do is to take the old version of the matrix, where I already have filled the empty spaces, and translate this into the new version. Then, the populating process merely looks if entries have changed and if so, alters them.

The situation from the question arises after I have translated the old version into the new one, before doing the delta. The new matrix, populated with the old information, is TABLE1. The delta process, over which I have no control, gives me column names and information to be entered into the cells of the matrix (this is table MAP). So I need to find the column in the matrix labeled by the delta process and then to change values in rows (which ones is specified via other information provided by the delta process)

CodePudding user response：

Dynamic SQL it is; here's an example, see if it helps.

This is a table whose contents should be modified:

SQL> select * from test order by id;

        ID NAME           SALARY
---------- ---------- ----------
         1 Little            100
         2                   200
         3 Foot                0
         4                     0

This is the map table:

SQL> select * from map;

COLUMN CB_MODIFY  VALUE WHERE_CLAUSE
------ ---------- ----- -------------
NAME   Y          Scott where id <= 3
SALARY N          1000  where 1 = 1

Procedure loops through all columns that are set to be modified, composes the dynamic update statement and executes it:

SQL> declare
  2    l_str varchar2(1000);
  3  begin
  4    for cur_r in (select m.column_name, m.value, m.where_clause
  5                  from map m
  6                  where m.cb_modify = 'Y'
  7                 )
  8    loop
  9      l_str := 'update test set ' ||
 10                cur_r.column_name || ' = ' || chr(39) || cur_r.value || chr(39) || ' ' ||
 11                cur_r.where_clause;
 12      execute immediate l_str;
 13    end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from test order by id;

        ID NAME           SALARY
---------- ---------- ----------
         1 Scott             100
         2 Scott             200
         3 Scott               0
         4                     0

SQL>