Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Unanswered: Return rows affected from DB2 MERGE in result set

    Hello All,

    I am using DB2 9.1 LUW and would like to retrieve the rows affected, along with the action (UPD, INS, DEL) from the MERGE command in a result set. Not just the number of rows, but the actual data from the rows. In DB2 9.1 for z/OS you can wrap the MERGE statement in a SELECT FROM statement, but this does not seem to be supported in DB2 LUW. Any other way to do this?

    Thanks in advance...

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although it would be not so efficient than MERGE, you can retrieve the rows UPDATEd, INSERTed and/or DELLETEd in common table expressions for each operations.

    Like this...
    Code:
    WITH
     undate_operation AS (
    SELECT ...
      FROM FINAL TABLE(UPDATE ...)
    )
    ,delete_operation AS (
    SELECT ...
      FROM OLD TABLE(DELETE ...)
    )
    ,insert_operation AS (
    SELECT ...
      FROM FINAL TABLE(INSERT ...)
    )
    SELECT ... FROM update_operation
    UNION ALL
    SELECT ... FROM delete_operation
    UNION ALL
    SELECT ... FROM insert_operation
    You should be careful to exclude deleted rows from insert operation and to exclude inserted rows from update/delete operation, accoding to sequence operations.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example.

    Prepare tables and data for test.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_merge_source
    ( id     INTEGER NOT NULL PRIMARY KEY
    , amount INTEGER
    , op     CHAR(3)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_merge_source
    VALUES
      (2 , 15 , 'ADD')
    , (3 , 30 , 'ZAP')
    , (4 ,  0 , 'DEL')
    , (5 , 60 , 'ADD');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE test_merge_target;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_merge_target
    ( id     INTEGER NOT NULL PRIMARY KEY
    , amount INTEGER
    , desc   VARCHAR(50)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_merge_target
    VALUES
      (1 , 11 , 'initial amount = 11')
    , (2 , 22 , 'initial amount = 22')
    , (3 , 33 , 'initial amount = 33')
    , (4 , 44 , 'initial amount = 44');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    MERGE and result of test_merge_target.
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO test_merge_target t
         USING test_merge_source s
           ON  s.id = t.id
    WHEN MATCHED
     AND s.op = 'ADD' THEN
         UPDATE
            SET amount = t.amount + s.amount
              , desc = desc || '; added amount = ' || s.amount
    WHEN MATCHED
     AND s.op = 'ZAP' THEN
         UPDATE
            SET amount = s.amount
              , desc = desc || '; replaced amount = ' || s.amount
    WHEN MATCHED
     AND s.op = 'DEL' THEN
         DELETE
    WHEN NOT MATCHED THEN
         INSERT VALUES (s.id , s.amount , 'inserted amount = ' || s.amount)
    ELSE IGNORE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_merge_target;
    ------------------------------------------------------------------------------
    
    ID          AMOUNT      DESC                                              
    ----------- ----------- --------------------------------------------------
              1          11 initial amount = 11                               
              2          37 initial amount = 22; added amount = 15            
              3          30 initial amount = 33; replaced amount = 30         
              5          60 inserted amount = 60                              
    
      4 record(s) selected.

    Use common table expressions to retrieve affected row.
    Code:
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE test_merge_target;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_merge_target
    ( id     INTEGER NOT NULL PRIMARY KEY
    , amount INTEGER
    , desc   VARCHAR(50)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_merge_target
    VALUES
      (1 , 11 , 'initial amount = 11')
    , (2 , 22 , 'initial amount = 22')
    , (3 , 33 , 'initial amount = 33')
    , (4 , 44 , 'initial amount = 44');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     updated AS (
    SELECT 'Updated' AS operation
         , s.*
      FROM FINAL TABLE
           (UPDATE test_merge_target t
               SET (amount , desc)
                 = (SELECT CASE s.op
                           WHEN 'ADD' THEN t.amount + s.amount
                           WHEN 'ZAP' THEN s.amount
                           END
                         , CASE s.op
                           WHEN 'ADD' THEN desc || '; added amount = ' || s.amount
                           WHEN 'ZAP' THEN desc || '; replaced amount = ' || s.amount
                           END
                      FROM test_merge_source s
                     WHERE s.id = t.id )
             WHERE EXISTS
                   (SELECT *
                      FROM test_merge_source s
                     WHERE s.id = t.id
                       AND s.op IN ( 'ADD' , 'ZAP' )
                   )
           ) s
    )
    ,deleted AS (
    SELECT 'Deleted' AS operation
         , s.*
      FROM OLD TABLE
           (DELETE FROM test_merge_target t
             WHERE EXISTS
                   (SELECT *
                      FROM test_merge_source s
                     WHERE s.id = t.id
                       AND s.op = 'DEL'
                   )
           ) s
    )
    ,inserted AS (
    SELECT 'Inserted' AS operation
         , s.*
      FROM FINAL TABLE
           (INSERT INTO test_merge_target
            SELECT s.id , s.amount , 'inserted amount = ' || s.amount
              FROM test_merge_source s
             WHERE NOT EXISTS
                   (SELECT *
                      FROM test_merge_target t
                     WHERE s.id = t.id)
               AND s.id
                   NOT IN (SELECT id FROM deleted)
           ) s
    )
    SELECT * FROM updated
    UNION ALL
    SELECT * FROM deleted
    UNION ALL
    SELECT * FROM inserted
    ;
    ------------------------------------------------------------------------------
    
    OPERATION ID          AMOUNT      DESC                                              
    --------- ----------- ----------- --------------------------------------------------
    Updated             2          37 initial amount = 22; added amount = 15            
    Updated             3          30 initial amount = 33; replaced amount = 30         
    Deleted             4          44 initial amount = 44                               
    Inserted            5          60 inserted amount = 60                              
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_merge_target;
    ------------------------------------------------------------------------------
    
    ID          AMOUNT      DESC                                              
    ----------- ----------- --------------------------------------------------
              1          11 initial amount = 11                               
              2          37 initial amount = 22; added amount = 15            
              3          30 initial amount = 33; replaced amount = 30         
              5          60 inserted amount = 60                              
    
      4 record(s) selected.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •