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 databases created in Vertica 9.3 and later, MERGE
writes directly to ROS. Databases created in earlier versions use a default load of AUTO.
For details, see Merging Table Data.
Syntax
MERGE [ /*+ hint[, hint] */ ] ... 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
/*+ hint[, hint]*/
|
One or both of the following hints: For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect. |
[database.]schema
|
Specifies a schema, by default 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. The total number of target table columns cannot exceed 831. |
source‑dataset |
The data to join to target‑table, one of the following:
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 |
matching‑clause |
One of the following clauses:
|
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:
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:
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. 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:
For details, see Merging Table Data in the Administrator's Guide. |
Privileges
MERGE
requires the following privileges:
SELECT
permissions on the source data andINSERT
,UPDATE
, andDELETE
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 grantsuser1
access to target tablet2
:
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:
- Design projections for optimal
MERGE
performance. - Facilitate creation of optimized query plans.
- Use a source data set that is smaller than the target table.
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:
- Identity/auto-increment columns, or columns whose default value is set to a named sequence.
- Vmap columns such as
__raw__
in flex tables.
Examples
In the Administrator's Guide, see: