Merging
The MERGE command runs insert or update operations on a target table from the results of a join with a source table.
SyntaxMERGE INTO <table_path>.<table_name> [AS target_alias]
USING {
(select_statement) |
<table_path>.<table_name> [AS source_alias]
}
ON ( condition )
[ WHEN MATCHED THEN
UPDATE SET * |
UPDATE SET <column1_name> = <value1> [, <column2_name> = <value2> ... ] ]
[ WHEN NOT MATCHED THEN
INSERT * |
INSERT [ (<column_name> [, ...]) ]
VALUES (expression [, ...]) ]
Parameters
<table_name>
String
The path in which the table is located.
<table_name>
String
The name of the table with data that you want to merge.
select_statement
String
Optional
The query that produces data that you want to merge.
ON ( condition )
String
Specifies the conditions on which source table joins with target table to determine whether and where they match.
WHEN MATCHED THEN
String
Optional
Specifies that all rows of target table which match the rows returned from the source table due to the search condition are either updated according to the UPDATE SET clause.
UPDATE SET *
String
Equivalent to UPDATE SET col1 = source.col1 [, col2 = source.col2 ...] for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.
UPDATE SET <column1_name> = <value1> [, <column2_name> = <value2> ... ]
String
Sets the value of one or more columns. The value can be any expression or sub-query that returns a single value.
WHEN NOT MATCHED THEN
String
Optional
Specifies that a row is inserted into target table for every row, returned from the source table due to the search condition, that does not match a row in the target table. The values to insert are specified by the INSERT clause.
INSERT SET *
String
Equivalent to INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...]) for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.
INSERT [ (<column_name> [, ...]) ] VALUES (expression [, ...])
String
Specifies the values to insert into a new row of the target table for every row, returned from the source table due to the search condition, that does not match a row in the target table.