Example: MERGE Updating a Primary Index - Teradata Database - Teradata Vantage NewSQL Engine

You cannot update primary index or partitioning column unless the source is a valid single-row subquery.

Suppose you have created the following tables:

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

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

The following case is valid:

     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 x1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

The following case is not valid because while the subquery guarantees a single row, no constant in specified in the ON clause equality condition, so you cannot update the primary index as this request attempts to do:

     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 x1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);