Merge Into (Delta Lake on Databricks)
Important
This documentation has been retired and might not be updated. The products, services, or technologies mentioned in this content are no longer supported. See MERGE INTO.
Merge a set of updates, insertions, and deletions based on a source table into a target Delta table.
MERGE INTO [db_name.]target_table [AS target_alias]
USING [db_name.]source_table [<time-travel-version>] [AS source_alias]
ON <merge-condition>
[ WHEN MATCHED [ AND <condition> ] THEN <matched-action> ]
[ WHEN MATCHED [ AND <condition> ] THEN <matched-action> ]
[ WHEN NOT MATCHED [ AND <condition> ] THEN <not-matched-action> ]
where
<matched-action> =
DELETE |
UPDATE SET * |
UPDATE SET column1 = value1 [, column2 = value2 ...]
<not-matched-action> =
INSERT * |
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
<time-travel-version> =
TIMESTAMP AS OF timestamp_expression |
VERSION AS OF version
In Databricks Runtime 5.5 LTS and 6.x,
MERGE
can have at most 2WHEN MATCHED
clauses and at most 1WHEN NOT MATCHED
clause.WHEN MATCHED
clauses are executed when a source row matches a target table row based on the match condition. These clauses have the following semantics.WHEN MATCHED
clauses can have at most onUPDATE
and oneDELETE
action. TheUPDATE
action inmerge
only updates the specified columns of the matched target row. TheDELETE
action will delete the matched row.Each
WHEN MATCHED
clause can have an optional condition. If this clause condition exists, theUPDATE
orDELETE
action is executed for any matching source-target row pair row only when the clause condition is true.If there are multiple
WHEN MATCHED
clauses, then they are evaluated in order they are specified (that is, the order of the clauses matter). AllWHEN MATCHED
clauses, except the last one, must have conditions.If both
WHEN MATCHED
clauses have conditions and neither of the conditions are true for a matching source-target row pair, then the matched target row is left unchanged.To update all the columns of the target Delta table with the corresponding columns of the source dataset, use
UPDATE SET *
. This is equivalent toUPDATE SET col1 = source.col1 [, col2 = source.col2 ...]
for all the columns of the target Delta table. Therefore, this action assumes that the source table has the same columns as those in the target table, otherwise the query will throw an analysis error.This behavior changes when automatic schema migration is enabled. See Automatic schema evolution for Delta Lake merge for details.
WHEN NOT MATCHED
clauses are executed when a source row does not match any target row based on the match condition. These clauses have the following semantics.WHEN NOT MATCHED
clauses can only have theINSERT
action. The new row is generated based on the specified column and corresponding expressions. All the columns in the target table do not need to be specified. For unspecified target columns,NULL
will be inserted.Each
WHEN NOT MATCHED
clause can have an optional condition. If the clause condition is present, a source row is inserted only if that condition is true for that row. Otherwise, the source column is ignored.If there are multiple
WHEN NOT MATCHED
clauses, then they are evaluated in order they are specified (that is, the order of the clauses matter). AllWHEN NOT MATCHED
clauses, except the last one, must have conditions.To insert all the columns of the target Delta table with the corresponding columns of the source dataset, use
INSERT *
. This is equivalent toINSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...])
for all the columns of the target Delta table. Therefore, this action assumes that the source table has the same columns as those in the target table, otherwise the query will throw an analysis error.Note
This behavior changes when automatic schema migration is enabled. See Automatic schema evolution for Delta Lake merge for details.
Important
A MERGE
operation can fail if multiple rows of the source dataset match and attempt to update the same rows of the target Delta table. According to the SQL semantics of merge, such an update operation is ambiguous as it is unclear which source row should be used to update the matched target row. You can preprocess the source table to eliminate the possibility of multiple matches. See the Change data capture example—it preprocesses the change dataset (that is, the source dataset) to retain only the latest change for each key before applying that change into the target Delta table.
Examples
You can use MERGE
for complex operations such as deduplicating data, upserting change data, applying SCD Type 2 operations. See Upsert into a Delta Lake table using merge for a few examples.