Example: Target Table Composite Primary Index - Analytics Database - Teradata Vantage

The following example shows the necessity of specifying an equality condition on the primary index (and also on the partitioning column set for a row-partitioned table) in the ON clause for a target table with a composite primary index:

Suppose you create the following two tables:

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

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

The following two MERGE requests are both valid:

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

     MERGE INTO t1
     USING t2
       ON x1=z2+10 AND y1=y2+20
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT INTO (x1,y1,z1) VALUES (z2+10,y2+20,x2);

The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2, but the INSERT specification updates y2 for x1 rather than duplicating the ON clause specification, so it returns an error to the requestor:

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

The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2+10, but the INSERT specification updates y2+20 for x1 rather than z2+10:

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