The MERGE statement provides the ability to perform update and insert operations within a single SQL request. MERGE can also perform index and referential integrity maintenance in a single pass, unlike the case where update and insert operations must be executed separately.
For example, suppose you create the following tables and then use a MERGE request to update and insert rows from source table t2 into target table t1:
USING (empno INTEGER, CREATE TABLE t1 ( a1 INTEGER, b1 INTEGER, c1 INTEGER); CREATE TABLE t2 ( a2 INTEGER, b2 INTEGER, c2 INTEGER); MERGE INTO t1 USING t2 ON a1=a2 WHEN MATCHED THEN UPDATE SET b1=b2 WHEN NOT MATCHED THEN INSERT (a2, b2, c2);
An EXPLAIN shows a merge with matched updates and unmatched inserts into OB.t1 from OB.t2 with a condition of ("OB.t1.a1 = OB.t2.a2").
This MERGE request can also be coded as the following semantically equivalent multistatement UPDATE INSERT request:
UPDATE t1 FROM t2 SET b1=b2 WHERE a1=a2 ;INSERT INTO t1 SELECT a2, b2, c2 FROM t2, t1 WHERE NOT (a1=a2);
A comparison the two EXPLAIN reports shows that the MERGE request would outperform the semantically equivalent UPDATE and INSERT multistatement request.
- MERGE Update to OB.t1 from OB.t2
- RETRIEVE step from OB.t2
Next, there is a JOIN step of the results to OB.t1 with a join condition of ("OB.t1.a1 <> a2").