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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.