Example: You Cannot Substitute the System-Derived PARTITION Column For the Partitioning Column Set For a MERGE Operation With a RPPI Target Table - Teradata Database - Teradata Vantage NewSQL Engine

Assume you have defined the following source and target relation definitions:

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER)
     PRIMARY INDEX (a1)
     PARTITION BY b1;

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER)
     PRIMARY INDEX (a2),
     UNIQUE INDEX(b2);

The following example fails because you cannot substitute the system-derived PARTITION column for the partitioning column set of the target relation in the ON clause.

     MERGE INTO t1
       USING t2
       ON a1=a2 AND t1.PARTITION=10
     WHEN MATCHED THEN
       UPDATE SET b1=10
     WHEN NOT MATCHED THEN
       INSERT (a2, b2, c2);

This MERGE request is valid because it specifies conditions on the primary index of the target table, a1, and its partitioning column, b1:

     MERGE INTO t1
       USING t2
       ON a1=a2 AND b1=10
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

The following MERGE request, again written against the same set of source and target tables, is not valid because it fails to specify the partitioning column for the target table, b1, in its ON clause.

     MERGE INTO t1
       USING t2
       ON a1=a2
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

You can specify a system-derived PARTITION column-based condition in the ON clause, but only as a residual condition. For example, the following example works correctly because the primary index equality condition a1=a2 and the target table partitioning column condition b1=10 are both specified. The system treats the additional t1.PARTITION condition, t1.PARTITION=25 as a residual condition only.