MERGE and Upsert

## The problem MERGE solves Loading data into a table that already holds rows almost always means the same thing: *insert the row if it is new, otherwise update the row that is already there*. Written with ordinary DML that becomes two statements — an `UPDATE` for the keys that exist plus an `INSERT ... SELECT` for the keys that do not — and application code often degenerates further into a per-row "try update, check the row count, insert if zero" loop. `MERGE`, standardized in SQL:2003, expresses the whole thing as one set-based statement over an entire source rowset. ## Anatomy of the statement ```sql MERGE INTO customer_dim t USING staging_customer s ON (t.customer_id = s.customer_id) WHEN MATCHED THEN UPDATE SET name = s.name, city = s.city WHEN NOT MATCHED THEN INSERT (customer_id, name, city) VALUES (s.customer_id, s.name, s.city); ``` Four parts: - **`MERGE INTO <target> [alias]`** — the only table the statement modifies. - **`USING <source> [alias]`** — a table, a view, a derived table (a parenthesised `SELECT` with an alias), or a `VALUES` list. It is read-only. - **`ON (<condition>)`** — the *match* condition. It is a join predicate, not a filter. - **One or more `WHEN ...` arms** — the actions. ## How rows are classified Conceptually the engine evaluates the ON condition between the source and the target. For each source row: if at least one target row satisfies ON, that source row is **matched**; otherwise it is **not matched**. Matched source rows drive `UPDATE` or `DELETE` against the matched target row; unmatched ones drive `INSERT` of a brand-new target row. Exactly one arm fires per row, and the classification is made against the target as it stood when the statement began — a row this same statement inserts is not then visible as a match for a later source row. ## The arms - `WHEN MATCHED THEN UPDATE SET col = expr, ...` — the assignment targets are target columns, written unqualified; the right-hand expressions may reference both the target and the source alias. There is no `FROM`, no `WHERE`: the arm applies to the matched target row. - `WHEN MATCHED THEN DELETE` — removes the matched target row. This is what lets a single MERGE apply a delta feed that contains deletions. - `WHEN NOT MATCHED THEN INSERT (cols) VALUES (exprs)` — the value expressions may reference source columns, constants and functions only, because there is no matched target row to read from. Any arm may carry an extra condition, as in `WHEN MATCHED AND s.op = 'D' THEN DELETE`, and arms of the same kind may be repeated; they are tested in written order and the first one whose condition holds wins. ## What MERGE does not give you MERGE has no `WHERE` clause of its own — the only way to narrow the source is inside the `USING` query, and narrowing the *target* through the ON condition is a classic trap, because a target row excluded by ON is reported as *not matched* and gets inserted a second time. Standard MERGE also has no arm for target rows the source knows nothing about; the "not matched by source" direction is an engine extension where it exists at all (SQL Server spells it `WHEN NOT MATCHED BY SOURCE`). And a target row may be acted on only once: if two source rows match the same target row, the statement is in error rather than picking a winner, so the source usually has to be collapsed to one row per key first. ## Portability MERGE is genuinely part of the SQL standard and is implemented by Oracle, SQL Server, Db2 and PostgreSQL (added in PostgreSQL 15). MySQL and SQLite have no MERGE statement at all; they offer INSERT-side upsert clauses instead, which are dialect extensions with different matching rules. So "standard" here means "in the standard", not "available everywhere" — check the target engine before writing MERGE into portable code.