Example: MERGE With ON Clause and UPI or USI - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Consider the following table definitions:

     CREATE TABLE t1 (
       x1 INTEGER,
       y1 INTEGER,
       z1 INTEGER)
     PRIMARY INDEX (x1)
     PARTITION BY y1;

     CREATE TABLE t2 (
       x2 INTEGER,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX (x2)
     UNIQUE INDEX (y2);

When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause specifies an equality condition on the primary index of the target table as a constant, the INSERT specification might or might not match the constant value specified in the ON clause. Either specification is valid, as the following two requests show.

The following MERGE request is valid even though the primary index value specified in the INSERT specification does not match the ON clause primary index specification:

     MERGE INTO t1
     USING (SELECT *
            FROM t2
            WHERE y2=10) AS s
       ON x1=10
     WHEN MATCHED THEN
       UPDATE SET z1=z2
     WHEN NOT MATCHED THEN
       INSERT (x2, y2, z2);

The following MERGE request is valid because the primary index value specified in the INSERT specification matches the ON clause primary index specification:

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON x1=10 AND y1=z2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (10, z2, x2);

When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause has an equality condition on the primary index of the target table that is not a constant, the INSERT clause primary index specification must match the primary index value for the target table specified in the ON clause. The UPDATE specification also must not update the primary index of the target table.

The following MERGE request is valid because the primary index value specified in the INSERT specification, y2, matches the primary index value specified in the ON clause, and the UPDATE specification does not update the primary index of t1:

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON x1=y2 AND y1=z2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

The following MERGE request fails because the source relation is not guaranteed to be a single row. The failure to guarantee a single row occurs because z2 is neither a UPI nor a USI, so the INSERT specification must match the ON clause specification, which it does not.

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE z2=10) AS s
       ON x1=10 AND y1=20
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);