How to insert updated rows in a new table on DB2

I need to create an update using sql dynamic and all the updated rows have to be sent in a log table. In microsoft, i can use OUTPUT clause and it inserts the updated rows in a table, but how can i do this in db2, using sql dynamic?

I have the following tables:

AllCustomers - contains all customers from a db

Id	Name
1	John
2	Test

gdpr_id. - contains all customers which should be updated

Id	Name
1	John

gdpr_log - should contain the output of the update stmt

Id	Name
1	John

I found the below syntax , but it just displays the results.

SELECT fields FROM FINAL TABLE
(update table set field = 'value' where id ='xyz')

I tried to create another dynamic stmt as

INSERT INTO 
SELECT fields FROM FINAL TABLE
(update table set field = 'value' where id ='xyz')

and the syntax is not recognized. How can i replace it to insert all the updated values in a log table?

I have to use sql dynamic because the tables which need to be updated are stored in a metadata table and with a cursor, i create the update script for each line from the metadata table.

UPDATE: Metadata table looks like this:

table	column
AllCustom	Name
AllCustom	Lastname

CREATE OR REPLACE PROCEDURE sp_test ()
    DYNAMIC RESULT SETS 1
P1: BEGIN

    --*****************VARIABLES *****************
    DECLARE EOF INT DEFAULT 0;
    declare v_table nvarchar(50);
    declare v_column nvarchar(50);
    declare v_rowid nvarchar(50);
    declare v_stmt nvarchar(8000);
    declare s1 statement;
    
    
    --*****************UPDATE STEP *****************
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH HOLD WITH RETURN FOR 
        SELECT table,column FROM metadata_tbl;
    declare c1 cursor for s1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EOF = 1;
 
    OPEN cursor1; 
    
      WHILE EOF = 0 DO
        FETCH FROM cursor1 INTO v_table,v_column;
        
        
        SET v_stmt = 'WITH A AS
                            (
                              SELECT name
                              FROM FINAL TABLE
                              (
                                UPDATE ' || v_table || ' set ' || v_column || ' = ''some name'' where id in (select ID from gdpr_id )
                              )
                            )
                        SELECT COUNT (1) as tst
                            FROM FINAL TABLE
                            (
                              INSERT INTO GDPR_LOG (table,name, LOGDATE)
                              SELECT ''' || v_table || ''', name, current_timestamp from A
                            ) B';
                                    
            
        PREPARE s1 FROM v_stmt ;
        open c1 using v_table,v_column; 
        close c1;
                     
       END WHILE;
    CLOSE cursor1;              
     
END P1

Update step works fine, insert step duplicates the rows inserted. What should I do to have the insert step ok?

CodePudding user response：

You have to use SELECT as an outermost statement and keep inner SELECTs in distinct CTEs, if you have a number of them.
Try this:

WITH A AS
(
  SELECT ID, NAME
  FROM FINAL TABLE
  (
    UPDATE GDPR
    SET NAME = 'Some name'
    WHERE ID = 1
  )
)
SELECT COUNT (1)
FROM FINAL TABLE
(
  INSERT INTO GDPR_LOG (ID, NAME)
  SELECT * FROM A
) B

Update:
Using dynamic SQL.
You must enclose the whole statement with some statement termination character (say, @) different from the default one (;) if you use some tool to run this compound statement and specify this statement terminator correctly there.

BEGIN
  DECLARE C1 CURSOR FOR S1;
  
  PREPARE S1 FROM  
'
WITH A AS
(
  SELECT ID, NAME
  FROM FINAL TABLE
  (
    UPDATE GDPR
    SET NAME = ?
    WHERE ID = ?
  )
)
SELECT COUNT (1)
FROM FINAL TABLE
(
  INSERT INTO GDPR_LOG (ID, NAME)
  SELECT * FROM A
) B
';
  
  OPEN C1 USING 'Name', 1;
  CLOSE C1;
    
END