USING Clause
Syntax element … |
Specifies … |
USING |
an introduction to the table expression that defines the source table rows for the merge operation. |
using_expression |
an expression defining the columns of the table expression that specifies the source table rows for the merge as a list of using_expression values. The table expression representing the source table rows is composed of the comma-separated list of using_expression values. It is not a single value expression. using_expression can reference any of the following: For example, you can use the target table as the source table, but in that case, the target table becomes another instance table that contains the row data as it was prior to the update to the target table by the MERGE operation (see “Example 18: Using the Target Table as the Source Table” on page 457). using_expression cannot reference columns in any other base table. |
subquery |
a subquery table expression that specifies the source table for the merge. You can specify host variables in the WHERE clause, but not in the select list, of subquery. All host variables must be preceded by a COLON character. If subquery returns no rows, the source table is empty, no triggers are fired (see “MERGE as a Triggering Action” on page 444), and the merge operation performs neither update nor insert operations on target_table. subquery can reference any of the following: subquery cannot specify an ORDER BY clause. The WHERE clause for subquery must include an equality condition on the UPI or USI of the queried table to ensure a singleton select. |
source_table_name |
the name of the source table to be merged into target_table. |
column_name |
optional names to be used for the source row columns defined by the corresponding using_expression or subquery select list expression or by source_table_name. If you do not specify any column names, MERGE uses the column names specified in the using_expression or subquery of the USING clause. Source row columns, qualified by source_table_name, can be referenced in match_condition or in an update_expression or insert_expression. |
AS correlation_name |
an optional correlation name for the source table specified by using_expression, subquery, or source_table_name. |