ON Clause
|
Syntax element … |
Specifies … |
|
ON match_condition |
a conditional expression that determines whether the source row matches a given row in the target table. If the condition is met for any target rows and a WHEN MATCHED clause is specified, then the matching target rows are updated or deleted. match_condition must specify an equality constraint on the primary index of target_table to ensure that the candidate target row set can be hash‑accessed on a single AMP. The specified primary index value must match the primary index value implied by the column values specified in the WHEN NOT MATCHED clause. If the primary index value is the result of a using_expression, the expression cannot reference any column in target_table. Additionally, match_condition cannot specify subqueries or references to columns that do not belong to either the source table or to target_table. If target_table is a row-partitioned table, the values of the partitioning columns must also be specified in match_condition, and the WHEN NOT MATCHED clause must specify the same partitioning column values as match_condition. Host variables are permitted in match_condition. All host variables must be preceded by a COLON character. match_condition cannot reference a table that is neither the target table nor the source table. |
|
WHEN MATCHED THEN |
an introduction to the operation to be performed on matching rows. You can specify WHEN MATCHED THEN and WHEN NOT MATCHED THEN clauses in any order. |
|
UPDATE SET UPD SET |
an update set clause operation to be performed for matching rows. |
|
update_column = update_expression |
an equality condition on a target table column (specified by update_column) that defines how the specified field is to be updated. update_expression produces a new value to be placed into the field to be updated. update_expression can include source table column references, target table column references, a constant, a null expressed by the reserved word NULL, a DEFAULT function, host variables, or an arithmetic expression for calculating the new value. All host variables must be preceded by a COLON character. You cannot specify a derived period column name. |
|
DELETE |
that matching rows are deleted. Note: DELETE cannot be combined with INSERT or UPDATE in a MERGE statement. |
|
WHEN NOT MATCHED THEN |
an introduction to the operation to be performed on nonmatching rows. You can specify WHEN MATCHED and WHEN NOT MATCHED clauses in any order. You can also specify a WHEN NOT MATCHED clause without also specifying a WHEN MATCHED clause. |
|
INSERT INS |
an introduction to a value list to be inserted for nonmatching rows. Because an inserted row might be a duplicate of an existing row in target_table, its acceptance depends on whether target_table is defined to be SET or MULTISET. |
|
VALUES |
an optional keyword for the value list. |
|
insert_column |
a column name into which a corresponding value in the value list is to be inserted for nonmatching rows. |
|
insert_expression |
a value to be inserted into a corresponding insert_column for nonmatching rows. Host variables are permitted in insert_expression. All host variables must be preceded by a COLON character. |