DB 2のMERGE文使用上の注意!
MERGE INTO project px
USING (SELECT project.* FROM SYSIBM.DUAL LEFT JOIN project WHERE id = ?) py
ON px.id = py.id
WHEN MATCHED THEN
UPDATE SET
(name, description, last_changed_time) = (?, ?, ?)
WHEN NOT MATCHED THEN
INSERT (name, description, last_changed_time)
VALUES (?, ?, ?)
ELSE IGNORE;
MERGE INTO project px
USING (SELECT 1 AS VAL FROM SYSIBM.DUAL WHERE 1 = 2) py
ON VAL = 1
WHEN MATCHED THEN
SIGNAL SQLSTATE 'ZZ110'
SET MESSAGE_TEXT = 'Kidding you?'
WHEN NOT MATCHED THEN
SIGNAL SQLSTATE 'ZZ911'
SET MESSAGE_TEXT = 'Kidding me?'
ELSE IGNORE;
-- , MATCHED, NOT MATCHED
MERGE INTO project px
USING (SELECT 1 AS VAL FROM SYSIBM.DUAL WHERE 1 = 1) py
ON VAL = 1
WHEN MATCHED THEN
SIGNAL SQLSTATE 'ZZ110'
SET MESSAGE_TEXT = 'Kidding you?'
WHEN NOT MATCHED THEN
SIGNAL SQLSTATE 'ZZ911'
SET MESSAGE_TEXT = 'Kidding me?'
ELSE IGNORE;
-- MATCHED
-- Application raised error or warning with diagnostic text: "Kidding you?".. SQLCODE=-438, SQLSTATE=ZZ911
MERGE INTO project px
USING (SELECT 1 AS VAL FROM SYSIBM.DUAL WHERE 1 = 1) py
ON VAL = 2
WHEN MATCHED THEN
SIGNAL SQLSTATE 'ZZ110'
SET MESSAGE_TEXT = 'Kidding you?'
WHEN NOT MATCHED THEN
SIGNAL SQLSTATE 'ZZ911'
SET MESSAGE_TEXT = 'Kidding me?'
ELSE IGNORE;
-- NOT MATCHED
-- Application raised error or warning with diagnostic text: "Kidding me?".. SQLCODE=-438, SQLSTATE=ZZ110
上記の3つの例のELSE IGNOREはいずれも無視でき、最終結果には影響しない.
公式文書参照:http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm