Section 9.4. The MERGE Statement

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

 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 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.

Figure 9.26. Syntax of the MERGE statement

 >>-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-. >--+-------------+--------------------------------------------->< 

NOTE

Refer to Chapter 1, Introduction to DB2 UDB, for a description of the DB2 syntax diagram conventions.