Learning Visually with Examples [Electronic resources]

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

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