MERGE / UPDATE / DELETE

Databricks · Delta Lake

Theory

Delta tables support row-level changes in SQL and Python. UPDATE changes columns for rows that match a condition, DELETE removes matching rows, and MERGE INTO combines both with inserts: it joins a source (new or changed data) to the target on a key and, for each row, runs WHEN MATCHED THEN UPDATE (or DELETE), WHEN NOT MATCHED THEN INSERT, and optionally WHEN NOT MATCHED BY SOURCE for target rows missing from the source. This is the standard way to upsert and to apply CDC feeds.

Under the hood Delta rewrites only the files that contain affected rows, or, with deletion vectors enabled, just marks the old rows as removed, then commits a new version atomically. For example, merging updates (102, 850) and (106, 700) into orders updates order 102 and inserts order 106 in one transaction. Each source row must match at most one target row, otherwise MERGE fails.

Why we use it

Data warehouses need upserts: late-arriving updates, slowly changing dimensions and change data capture. Without MERGE you would have to rewrite whole tables or partitions by hand, which is slow and risky.

When to use it

Use MERGE for upserts and CDC; UPDATE or DELETE for simple fixes and GDPR-style deletions. For append-only data, plain INSERT or append is cheaper. Deduplicate the source first so each key appears once.

How it works

  1. Read the source rows
  2. Join source to target on the merge key
  3. Does the source row match a target row? Yes: WHEN MATCHED: update (or delete) the target row. No: WHEN NOT MATCHED: insert it.
  4. Write changed files or deletion vectors
  5. Commit one new table version

Syntax

MERGE INTO target t USING source s ON t.key = s.key
  WHEN MATCHED THEN UPDATE SET * | UPDATE SET col = s.col | DELETE
  WHEN NOT MATCHED THEN INSERT * | INSERT (cols) VALUES (...)
  WHEN NOT MATCHED BY SOURCE THEN DELETE
UPDATE t SET col = expr WHERE cond
DELETE FROM t WHERE cond
Python: DeltaTable.forName(spark, name).merge(src, cond).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Example

MERGE INTO main.sales.orders AS t
USING main.sales.order_updates AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET t.region = s.region, t.amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, region, amount) VALUES (s.order_id, s.region, s.amount);

UPDATE main.sales.orders SET region = 'North' WHERE order_id = 104;
DELETE FROM main.sales.orders WHERE order_date < '2020-01-01';

# Python
from delta.tables import DeltaTable
DeltaTable.forName(spark, "main.sales.orders").alias("t") \
  .merge(updates_df.alias("s"), "t.order_id = s.order_id") \
  .whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic