Primary Index of the Target Table - Teradata Database

The primary index of the target table cannot be an identity column if you stipulate an INSERT specification and the ON clause predicate specifies an equality condition with the target table primary index (and with its partitioning column if it has row partitioning), and the expression specifies only source table columns.

Following are exceptions to this rule:

The source relation is a valid single‑row subquery.

The request does not specify an INSERT specification and the primary condition in the ON clause is an equality constraint.

You do not specify an INSERT clause if the MERGE request has an equality condition with the primary index of the target table (and partition column set, if the target table has row partitioning).

See “Example 12: MERGE and Identity Columns” on page 453 for three valid examples.

Consider the following target and source table definitions. Note that target table t1 defines an identity column on its default primary index, which is column x1.

     CREATE TABLE t1 (

       x1 INTEGER GENERATED BY DEFAULT AS IDENTITY, 

       y1 INTEGER, 

       z1 INTEGER); 

     CREATE TABLE t2 (x2 INT, 

       y2 INTEGER, 

       z2 INTEGER) 

     PRIMARY INDEX (x2) 

     UNIQUE INDEX (y2);

The following MERGE request is valid because its source relation s, based on a projection of t2, is a valid single‑row subquery:

     MERGE INTO t1

     USING (SELECT x2, y2, z2 

            FROM t2 

            WHERE y2=10) AS s

       ON x1=10 AND y1=20

     WHEN MATCHED THEN

       UPDATE SET z1=10

     WHEN NOT MATCHED THEN

       INSERT (y2, z2, x2);

For the following MERGE request, if a constant is not specified, you would have to follow ON clause rule 1 for the case where a WHEN NOT MATCHED clause is not specified, which would render the request to be nonvalid. However, this example specifies the constant value 10 in the WHEN MATCHED clause, so it is valid.

     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;