Example: MERGE With ON Clause and UPI or USI - 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
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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);