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

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™

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.