If the source relation is defined by a subquery, the subquery must conform to the restrictions for derived tables (see “Rules and Restrictions for Derived Tables” on page 98), which include not specifying any of the following SQL elements:
The following MERGE request is not valid because it specifies a WITH … BY clause in its source relation subquery:
MERGE INTO t1
USING (SELECT a2, b2, c2
FROM t2
WITH SUM(b2) BY c2) AS source (a2, b2, c2)
ON a1=a2
WHEN MATCHED THEN
UPDATE SET c1=c2;
The following MERGE request is not valid because it specifies an ORDER BY clause in its source relation subquery:
MERGE INTO t1
USING (SELECT a2, b2, c2
FROM t2
ORDER BY b2) AS source (a2, b2, c2)
ON a1=a2
WHEN NOT MATCHED THEN
INSERT (a2,b2,c2);
If you specify the source table using a subquery, then the select list of that subquery cannot reference columns from the derived table it creates.
If the UPDATE source table contains more than one matching row for any given row in the target table, it is an error.
For example, suppose you have defined the following target and source tables:
CREATE TABLE target (
a INTEGER,
b INTEGER);
CREATE TABLE source (
c INTEGER,
d INTEGER);
You then populate the tables using the following INSERT requests:
INSERT INTO target VALUES (1,1);
INSERT INTO source VALUES (1,2);
INSERT INTO source VALUES (1,3);
Next you perform the following MERGE request:
MERGE INTO target AS t
USING (SELECT c, d
FROM source) AS s
ON t.a=s.c
WHEN MATCHED THEN
UPDATE SET b=s.d;
Assume that the target table looks like this before it is updated. Note that the values for column d are ordered differently for version 1 and version 2 of the source table:
|
|
source - version 1 |
|
target |
||
|
|
c |
d |
|
a |
b |
|
|
1 |
2 |
|
1 |
1 |
|
|
1 |
3 |
|
|
|
|
|
source - version 2 |
|
|
|
c |
d |
|
|
1 |
3 |
|
|
1 |
2 |
The outcome of this MERGE update operation depends on the order of the rows in source. For example, compare the result of the update on target using version 1 of source versus the result of the update on target using version 2 of source.
Using version 1:
|
|
target |
|
|
|
a |
b |
|
|
1 |
2 |
Using version 2:
|
|
target |
|
|
|
a |
b |
|
|
1 |
3 |
As you can see, depending on the order of the rows in source, the final value for column b in target can be either 2 or 3, proof of nondeterministic behavior.
This is an error condition.
To avoid this error, you must collapse multiple matching rows in the source table into a single row beforehand. For example, you can aggregate multiple rows incrementing a column value in the source_table_name reference using a GROUP BY clause.
If the source is guaranteed to be a single row (that is, you specify an explicit value list or it is single table subquery with a UPI or USI constraint specified), then the ON clause predicate can have an equality condition between the primary index and a constant value, and the partitioning column set (if the target is a row‑partitioned table) and a constant value.
The INSERT specification can either match or not match with the constant specified in the ON clause equality condition.
When the USING subquery guarantees a single‑row source relation by specifying an equality condition on a unique index, you do not need to specify the partitioning column set in the subquery. See “Example 17: With a Guaranteed Single‑Row Source Relation, You Do Not Need To Specify the Partitioning Column Set” on page 456.