Example: ON Clause Conditions Must Be ANDed With The Primary Index and Partitioning Column Equality Constraints - 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™

The following examples show the proper and improper specification of ON clause conditions in a MERGE request.

Consider the following table definitions, with t1 being the target relation and t2 being the source relation for all the examples that follow:

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

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

The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request, b1=b2:

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

The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request c1+c2=1 OR b1+b2=1.

Even though the second condition is internally disjunctive, the result it evaluates to is ANDed with the primary index condition.

     MERGE INTO t1
     USING t2
       ON a1=a2 AND (c1+c2=1 OR b1+b2=1)
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

The following example is not valid. It aborts and returns an error message to the requestor because the primary index equality constraint a1=a2 is ORed with the other specified condition in the request, c1=c2.

The primary index equality constraint, as well as the partitioning column equality constraint if the target table has a partitioned primary index, must always be ANDed to any other conditions you specify in the ON clause.

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