Upserts rows into a Delta table from a source. Separate clauses decide each row's fate: WHEN MATCHED typically updates, WHEN NOT MATCHED inserts, and WHEN NOT MATCHED BY SOURCE can delete target rows absent from the source.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains MERGE INTO in context, with comparison tables and the common traps.
Terms in this definition
- Delta table
The default kind of table on Databricks, kept in Delta Lake format. Streams can read from it using
spark.readStream.table(...)and can also write to it. - WHEN MATCHED
Clauses inside
MERGE INTOthat define the action for each source row, according to whether or not it finds a matching target row. - WHEN NOT MATCHED BY SOURCE
Handles rows in the target that have nothing corresponding in the source during
MERGE INTO, available from Databricks SQL or Runtime 12.2 LTS. Given a condition, it clears out-of-date chunks that AI Search could otherwise still return. - CRUD
Shorthand for create, read, update and delete, the four basic things you do with data. Data-plane roles in Azure Cosmos DB, for instance, authorise those operations on items.
Related terms
- MERGE
A SQL command that compares a source with a target table and, depending on whether rows match, inserts, updates or deletes them. Fabric Warehouse supports it as generally available, and Spark SQL offers MERGE INTO for Delta tables.