The following rules apply to updating the primary index and partitioning column of a target table:
You cannot update the primary index or partitioning column of the target table.
Consider the following target and source table definitions:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER);
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX(x2)
UNIQUE INDEX (y2);
The following MERGE request is valid because the source relation is a single row due to its WHERE clause specifying a constant for the USI column y2:
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 MERGE request is not valid because even though the source relation s (derived from t2) is a single row, the ON clause does not specify a constant condition, which violates ON clause rule 2, and the primary index of target table t1, x1, is updated, which violates the nonupdatability rule on primary index and partitioning column expressions.
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);