| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-22-10, 15:43
|
|
Registered User
|
|
Join Date: Feb 2010
Posts: 1
|
|
|
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...
|
|
03-07-10, 20:55
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
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.
|
|
03-07-10, 21:06
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Return rows affected from DB2 MERGE in result set
