MERGE statement
MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.
Overview
MERGE INTO (sometimes just MERGE) is the SQL-standard statement for synchronizing a target table with a source table or query result, applying different actions depending on whether each source row matches an existing target row:
Copy code
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
INSERT (id, value) VALUES (s.id, s.value);
Unlike INSERT ... ON CONFLICT, MERGE doesn't require a declared primary key or unique constraint — the match condition in the ON clause can be any expression. It can also express deletes (WHEN MATCHED THEN DELETE) and multiple conditional branches with additional predicates, making it well-suited for slowly changing dimension (SCD) loads and change-data-capture (CDC) pipelines.
DuckDB specifics
DuckDB added MERGE INTO support in version 1.4. Its syntax follows the pattern above, with a few DuckDB-specific extensions: a WHEN NOT MATCHED BY SOURCE clause to handle target rows absent from the source (e.g., to delete them), and a RETURNING clause that reports which action (merge_action) was taken per row:
Copy code
MERGE INTO people
USING (SELECT 1 AS id, 98000.0 AS salary) updates
ON updates.id = people.id
WHEN MATCHED THEN UPDATE SET salary = updates.salary
WHEN NOT MATCHED THEN INSERT
RETURNING merge_action, *;
DuckDB's MERGE INTO also works against Iceberg tables via the DuckDB-Iceberg extension, letting you apply an entire changeset (inserts, updates, and deletes) to an Iceberg table in one statement — useful for CDC-style pipelines writing into a lakehouse table format.
Related terms
The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
UPSERT →UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause.
INSERT statement →The INSERT statement is a fundamental SQL command used to add new rows of data into a table.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
Incremental model →An incremental model is a transformation that processes only new or changed source data on each run and merges it into an existing table, instead of rebuilding the whole table from scratch every time.
FAQS
INSERT ... ON CONFLICT requires a declared primary key or unique constraint to detect conflicts and can only insert-or-update. MERGE INTO matches source and target rows on any arbitrary condition and can express insert, update, and delete actions together in one statement.
Yes, as of DuckDB 1.4 (released 2025). It follows standard MERGE INTO syntax with USING/ON/WHEN MATCHED/WHEN NOT MATCHED clauses, plus DuckDB-specific extensions like WHEN NOT MATCHED BY SOURCE and a RETURNING clause.
