MERGE

Performs update and insert operations on a target table based on the results of a join with another data set, such as a table or view. The join can match a source row with only one target row; otherwise, Vertica returns an error.

For details, see Merging Table Data.

Syntax

MERGE [ /*+ LABEL */ ] 
    INTO [[database.]schema.]target‑table [ [AS] alias ] 
    USING source‑dataset
    ON  join-condition matching‑clause[ matching‑clause ]

Returns

Number of target table rows updated or inserted

Parameters

LABEL

Assigns a label to a statement in order to identify it for profiling and debugging.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
target‑table

The table on which to perform update and insert operations. MERGE takes an X (exclusive) lock on the target table during the operation.

The total number of target table columns cannot exceed 831.
source‑dataset

The data to join to target‑table, one of the following:

  • [[database.]schema.]table [ [AS] alias ]
  • [[database.]schema.]view [ [AS] alias ]
  • (subquery) sq-alias

The specified data set typically supplies the data used to update the target table and populate new rows. You can specify an external table.

ON join‑condition

The conditions on which to join the target table and source data set.

The Vertica query optimizer can create an optimized query plan for a MERGE statement only if the target table join column has a unique or primary key constraint. For details, see MERGE Optimization in the Administrator's Guide.
matching‑clause

One of the following clauses:

MERGE supports one instance of each clause, and must include at least one.
WHEN MATCHED THEN UPDATE

For each target‑table row that is joined (matched) to source‑dataset, specifies to update one or more columns:

WHEN MATCHED [ AND update-filter ] THEN UPDATE
  SET { column = expression }[,…]

update-filter optionally filters the set of matching rows. The update filter can specify any number of conditions. Vertica evaluates each matching row against this filter, and updates only the rows that evaluate to true. For details, see Update and Insert Filters in the Administrator's Guide.

Vertica also supports Oracle syntax for specifying update filters:

WHEN MATCHED THEN UPDATE
  SET { column = expression }[,…] 
  [ WHERE update‑filter ]

The following requirements apply:

  • A MERGE statement can contain only one WHEN MATCHED clause.
  • target‑column can only specify a column name in the target table. It cannot be qualified with a table name.

For details, see Merging Table Data in the Administrator's Guide.
WHEN NOT MATCHED THEN INSERT

For each source‑dataset row that is not joined (not matched) to target‑table, specifies to:

  • Insert a new row into target‑table.
  • Populate each new row with the values specified in values‑list.
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.

insert-filter optionally filters the set of non-matching rows. The insert filter can specify any number of conditions. Vertica evaluates each non-matching source row against this filter. For each row that evaluates to true, Vertica inserts a new row in the target table. For details, see Update and Insert Filters in the Administrator's Guide.

Vertica also supports Oracle syntax for specifying insert filters:

WHEN NOT MATCHED THEN INSERT
  [ ( column‑list ) ] VALUES ( values‑list 
  [ WHERE insert‑filter ]

The following requirements apply:

  • A MERGE statement can contain only one WHEN NOT MATCHED clause.
  • column‑list can only specify column names in the target table. It cannot be qualified with a table name.
  • Insert filter conditions can only reference the source data. If any condition references the target table, Vertica returns an error.

For details, see Merging Table Data in the Administrator's Guide.

Privileges

MERGE requires the following privileges:

  • SELECT permissions on the source data and INSERT, UPDATE, and DELETE permissions on the target table.
  • Automatic constraint enforcement requires SELECT permissions on the table containing the constraint.
  • SELECT permissions on the target table if the condition in the syntax reads data from the target table. The following example grants user1 access to target table t2:

For example, the following GRANT statement grants user1 access to target table t2. This allows user1 to run the MERGE statement that follows:

=> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t2 to user1;
GRANT PRIVILEGE

=>\c - user1
You are now connected as user "user1".

=> MERGE INTO t2 USING t1 ON t1.a = t2.a
WHEN MATCHED THEN UPDATE SET b = t1.b
WHEN NOT MATCHED THEN INSERT (a, b) VALUES (t1.a, t1.b);

Improving MERGE Performance

You can improve MERGE performance in several ways:

For details, see MERGE Optimization in the Administrator's Guide.

Constraint Enforcement

MERGE respects all enforced constraints in the target table. If the merge operation attempts to copy values that violate those constraints, MERGE returns with an error and rolls back the merge operation.

If you run MERGE multiple times using the same target and source table, each iteration is liable to introduce duplicate values into the target columns and return with an error.

Columns Prohibited from Merge

The following columns cannot be specified in a merge operation; attempts to do so return with an error:

Examples

See: