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.

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.

If a merge would violate a table or schema disk quota, the operation fails. For more information, see Disk quotas.

The target table cannot have columns of complex data types. The source table can, so long as those columns are not included in the merge operation.

Syntax

MERGE [ /*+LABEL (label-string)*/ ]
    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

Arguments

LABEL

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

[database.]schema

Database and schema. The default schema is public. 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 table must not contain columns of complex types.
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.
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.

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.

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.

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.

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.

For example, the following GRANT statement grants user1 access to the t2 table. 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.

Constraint enforcement

If primary key, unique key, or check constraints are enabled for automatic enforcement in the target table, Vertica enforces those constraints when you load new data. If a violation occurs, Vertica rolls back the operation and returns an error.

Columns prohibited from merge

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

  • IDENTITY columns, or columns whose default value is set to a named sequence.

  • Vmap columns such as __raw__ in flex tables.

  • Columns of complex types ARRAY, SET, or ROW.

Examples

See:

See also