The MERGE statement combines the UPDATE and INSERT statements into a single statement with two conditional test clauses:
You can also use the MERGE statement to delete rows by specifying: WHEN MATCHED, DELETE.
The following table explains the meaning of these conditional clauses:
|
IF this clause evaluates to TRUE … |
THEN MERGE … |
|
WHEN MATCHED |
updates a matching target table row with the set of values taken from the current source row. deletes a matching target table row. |
|
WHEN NOT MATCHED |
inserts the current source row into the target table. |
A MERGE statement can specify one WHEN MATCHED clause and one WHEN NOT MATCHED clause, in either order. You need not specify both. However, you must specify at least one of these clauses.
The Merge with Matched Updates and Unmatched Inserts AMP step performs inserts and updates in a single step. A merge with matched updates and unmatched inserts step can perform any of the following operations:
A merge with matched deletes performs a DELETE only.
The merge with matched updates and unmatched inserts step assumes that the source table is always distributed on the join column of the source table, which is specified in the ON clause as an equality constraint with the primary index of the target table and sorted on the RowKey.
The step does a RowKey-based Merge Join internally, identifying source rows that qualify for updating target rows and source rows that qualify for inserts, after which it performs those updates and inserts.
This step is very similar to the APPLY phase of MultiLoad because it guarantees that the target table data block is read and written only once during the MERGE operation.
The order of evaluating whether a source row should be inserted into the target table or whether a matching target table row should be updated with the value set taken from the source varies accordingly. Note that the result depends on the order in which you specify the two clauses.