Example: Target Table Composite Primary Index - 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 has an ON clause that specifies an equality condition where x1=z2, but the INSERT specification updates y2 for x1 rather than duplicating the ON clause specification. The request is invalid and 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);