MERGE Matching Clauses
MERGE
supports one instance of the following matching clauses:
Each matching clause can specify an additional filter, as described in Update and Insert Filters.
WHEN MATCHED THEN UPDATE SET
Updates all target table rows that are joined to the source table, typically with data from the source table:
WHEN MATCHED [ AND update-filter ] THEN UPDATE SET { target‑column = expression }[,…]
Vertica can execute the join only on unique values in the source table's join column. If the source table's join column contains more than one matching value, the MERGE
statement returns with a run-time error.
WHEN NOT MATCHED THEN INSERT
WHEN NOT MATCHED THEN INSERT
inserts into the target table a new row for each source table row that is excluded from the join:
WHEN NOT MATCHED [ AND insert-filter ] THEN INSERT [ ( column‑list ) ] VALUES ( values‑list )
column‑list is a comma-delimited list of one or more target columns in the target table, listed in any order. MERGE
maps column‑list columns to values‑list values in the same order, and each column-value pair must be compatible. If you omit column‑list, Vertica maps values‑list values to columns according to column order in the table definition.
For example, given the following source and target table definitions:
CREATE TABLE t1 (a int, b int, c int);
CREATE TABLE t2 (x int, y int, z int);
The following WHEN NOT MATCHED
clause implicitly sets the values of the target table columns a
, b
, and c
in the newly inserted rows:
MERGE INTO t1 USING t2 ON t1.a=t2.x WHEN NOT MATCHED THEN INSERT VALUES (t2.x, t2.y, t2.z);
In contrast, the following WHEN NOT MATCHED
clause excludes columns t1.b
and t2.y
from the merge operation. The WHEN NOT MATCHED
clause explicitly pairs two sets of columns from the target and source tables: t1.a
to t2.x
, and t1.c
to t2.z
. Vertica sets excluded column t1.b
. to null:
MERGE INTO t1 USING t2 ON t1.a=t2.x WHEN NOT MATCHED THEN INSERT (a, c) VALUES (t2.x, t2.z);