Example: Using MERGE for Update and Insert Operations Within a Single SQL Request - Teradata Database - Teradata Vantage NewSQL Engine

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.

Next, there is a JOIN step of the results to OB.t1 with a join condition of ("OB.t1.a1 <> a2").