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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.

In the UPDATE and INSERT multistatement request, the following steps are performed in parallel:
  • 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").