INSERT OR UPDATE determines if a row exists
by matching UNIQUE column values to the existing data values. If a
UNIQUE constraint violation occurs, INSERT OR UPDATE performs an update operation. The UNIQUE column value can be explicitly
specified in INSERT OR UPDATE or it can be the
result of a column default value or a computed value.
When INSERT OR UPDATE is run against a sharded
table, if the shard key is the same as or a subset of the UNIQUE KEY constraint, INSERT OR UPDATE performs an update operation.
If the INSERT OR UPDATE attempts to perform
an update because of any other unique values found (that are not the
shard key), the command fails with an SQLCODE -120 error due to the
unique constraint failure.
IDKEY Column Values
When using INSERT OR UPDATE, you can only
insert IDKEY column values, not update them. If the table has an IDKEY
index and another UNIQUE constraint, INSERT OR UPDATE matches these columns to determine whether to perform an insert
or an update. If the other key constraint fails, this forces INSERT OR UPDATE to perform an update rather than an insert.
However, if the specified IDKEY column values do not match the existing
IDKEY column values, this update fails and generates an SQLCODE -107
error, because the update is attempting to modify the IDKEY columns.
Consider a table with columns A, B, C, and D. The table has
a primary key of (A,B) in an environment where the primary key is
the IDKEY, and a UNIQUE constraint on columns (C,D).
SET OPTION PKEY_IS_IDKEY = TRUE
CREATE TABLE ABCD (
CONSTRAINT AB PRIMARY KEY (A,B),
CONSTRAINT CD UNIQUE (C,D))
The table also has two rows of data:
INSERT INTO ABCD SET A=1, B=1, C=2, D=2
INSERT INTO ABCD SET A=1, B=2, C=3, D=4
Suppose you try to insert this value:
INSERT OR UPDATE ABCD (A,B,C,D) VALUES (2,2,3,4)
Because the UNIQUE (C,D) constraint failed, this statement cannot
perform an insert. Instead, it attempts to update Row 2. The IDKEY
for Row 2 is (1,2), so the INSERT OR UPDATE statement attempts to
change the column A value from 1 to 2. Because you cannot change an
IDKEY value, the update fails with an SQLCODE -107 error.
Reset your environment to the default settings, where the primary
key is not the IDKEY.
SET OPTION PKEY_IS_IDKEY = FALSE