9.4. The MERGE Statement
A MERGE statement combines an INSERT statement with an UPDATE or DELETE statement. For example, if a row in table T1 also exists in table T2 , the existing row in T2 should be updated. If a row in T1 does not exist in T2 , it should be inserted into T2 . A new and efficient way to code this logic can be implemented with one statement: the MERGE statement. Figure 9.25 shows this MERGE statement.
Figure 9.25. Example of a MERGE statement
Figure 9.26 illustrates the syntax of a MERGE statement. The MERGE statement has a lot of intricate details; see the DB2 UDB SQL Reference manual for more examples and additional information.
MERGE INTO T2 as target
USING (SELECT ... FROM T1) AS source
ON target.id=source.id
WHEN NOT MATCHED THEN
INSERT INTO T2 ...
WHEN MATCHED THEN
UPDATE T2 SET ...
Figure 9.26. Syntax of the MERGE statement
Chapter 1, Introduction to DB2 UDB, for a description of the DB2 syntax diagram conventions.
>>-MERGE INTO--+-table-name-------+----------------------------->
+-view-name--------+
'-(--fullselect--)-'
>--+------------------------+--USING--table-reference----------->
'-| correlation-clause |-'
>--ON--search-condition----------------------------------------->
.----------------------------------------------------------------.
V |
>----WHEN--| matching-condition |--THEN--+-| modification-operation |+>
'-signal-statement---------'
.-ELSE IGNORE-.
>--+-------------+---------------------------------------------><