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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-12-13
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantageā„¢

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