Example: For a RPPI Table, the ON Clause Must Specify a Condition on the Partitioning Column and the INSERT Specification Must Match - Analytics Database - Teradata Vantage

When the target table of a MERGE operation has a row-partitioned primary index, the ON clause of the MERGE request must specify a condition on the partitioning column of the table and the order of the columns in the INSERT clause must be the same as the order you specify in the ON clause.

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);

You want to use the following MERGE request to insert or update rows in t1, which has a primary index defined on x1 and a partitioning expression defined on y1:

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

The request is successful because you have defined a condition on y1 in the ON clause (y1=y2) and the specified order of columns in the INSERT clause matches the ordering specified in the ON clause.