How does ANSI MERGE differ from a vendor upsert that keys on a unique-constraint conflict?

## Two mechanisms, one goal "Insert it, unless it is already there — then update it" has two implementations in the wild, and the difference is not cosmetic. **Predicate-based (MERGE).** You supply a source rowset and a boolean condition. The engine decides matched or not matched by evaluating that condition against the target: ```sql MERGE INTO inventory t USING shipment s ON (t.warehouse_id = s.warehouse_id AND t.sku = s.sku) WHEN MATCHED AND s.qty = 0 THEN DELETE WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty WHEN NOT MATCHED THEN INSERT (warehouse_id, sku, qty) VALUES (s.warehouse_id, s.sku, s.qty); ``` **Conflict-based (vendor upsert).** You write an ordinary INSERT and attach a fallback. The engine attempts the insert; if a unique or primary-key constraint would be violated, it runs the fallback action against the colliding row instead. PostgreSQL spells this `ON CONFLICT (col) DO UPDATE SET ...` (or `DO NOTHING`), with the would-be-inserted values readable through the `EXCLUDED` pseudo-table; MySQL spells it `ON DUPLICATE KEY UPDATE`, firing on any violated unique key. These are extensions, not standard SQL. ## The differences that actually matter when you write the statement **What defines "already there".** MERGE's match rule is whatever you write in ON — it can span several columns, use expressions, or reference a column with no index at all. The conflict form's match rule is a constraint: without a unique index or primary key on the relevant columns there is nothing to collide with, and the update path can never fire. That makes the conflict form safer in one respect (it can only ever touch one row, the one that collided) and less expressive in another (you cannot merge on an arbitrary condition). **Which actions are available.** Standard MERGE offers `UPDATE`, `DELETE` and `INSERT` arms, several of them, each with its own `AND` condition. The conflict clauses offer insert-or-update, plus a do-nothing option; they have no delete path, so a feed containing tombstone rows needs a separate DELETE statement. **Where the rows come from.** MERGE is built around a source query in `USING`, so joins, filters and aggregation over the incoming batch are natural. The conflict form is attached to an INSERT, which can of course be `INSERT ... SELECT`, but the shape reads as "one insert with a fallback" rather than "a two-sided reconciliation". **Naming the incoming values.** In MERGE you refer to the source by its alias (`s.qty`). In the conflict form the incoming row has an engine-specific name — PostgreSQL's `EXCLUDED`, for example. Any expression that mixes old and new values, such as `qty = t.qty + s.qty`, has to be written in whichever vocabulary the form provides. **Self-conflicting batches.** Both forms dislike a batch that contains the same key twice, and both surface it differently — MERGE as a cardinality violation, the conflict form typically as an error about the same row being affected twice. Deduplicate the incoming rows either way. ## Portability, honestly stated MERGE is in the SQL standard and is implemented by Oracle, SQL Server, Db2 and PostgreSQL 15 and later. MySQL and SQLite have no MERGE statement; they only offer the INSERT-side clause. So "use the standard" is not automatically the portable choice here: if your code must run on MySQL, MERGE is not available, and if it must run everywhere, neither form is. Teams that need real portability usually either keep the upsert behind a small dialect-specific layer, or fall back to the fully portable two-statement shape — an UPDATE joined to the staging set, then an INSERT of the rows that still do not exist — accepting that it reads as two steps. ## Choosing Use the conflict form when there is a genuine unique key, the semantics really are insert-or-update, and you are writing for one engine — it is shorter and the intent is obvious. Use MERGE when the reconciliation needs several arms (update changed rows, delete tombstoned ones, skip no-ops), when the match rule is richer than a single constraint, or when the target engine supports it and the code will not move. And in either case, keep the deduplication of the incoming batch in the source query, because both forms punish a duplicate key rather than resolving it for you.