17.05 - Example: MERGE Updating a Primary Index - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

You cannot update primary index or partitioning column unless the source is a valid single-row subquery.

Suppose you have created the following tables:

     CREATE TABLE t1 (
       x1 INTEGER,
       y1 INTEGER,
       z1 INTEGER)
     PRIMARY INDEX(x1);

     CREATE TABLE t2 (
       x2 INTEGER,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX(x2)
     UNIQUE INDEX(y2);

The following case is valid:

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

The following case is not valid because while the subquery guarantees a single row, no constant in specified in the ON clause equality condition, so you cannot update the primary index as this request attempts to do:

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