Merging Table Data

MERGE statements can perform update and insert operations on a target table based on the results of a join with a source data set. The join can match a source row with only one target row; otherwise, Vertica returns an error.

MERGE has the following syntax:

MERGE INTO target‑table USING source‑dataset ON  join-condition
   matching‑clause[ matching‑clause ]

Merge operations have at least three components:

• The target table on which to perform update and insert operations. MERGE takes an X (exclusive) lock on the target table until the merge operation is complete.
• Join to another data set, one of the following: a table, view, or subquery result set.
• One or both matching clauses: WHEN MATCHED THEN UPDATE SET and WHEN NOT MATCHED THEN INSERT.