How Do Upserts Work (MERGE and Friends)?

An upsert is a single statement that inserts a row, or updates it if a row with the same key already exists. It exists because the alternative is a read-then-write in application code, which is a race: two concurrent requests can both see that no row exists and both insert, and one of them violates a unique constraint.

There are three native spellings. The standard SQL:2003 form is INSERT ... ON CONFLICT (key) DO UPDATE SET ..., supported by PostgreSQL and SQLite. MySQL and MariaDB use INSERT ... ON DUPLICATE KEY UPDATE ..., where the conflicting column is not named — the engine works out which unique index was violated. SQL Server and Oracle have the full MERGE statement, which can insert, update, delete and do it all in one pass with a source table.

The difference in the spellings is more than cosmetic. ON CONFLICT requires you to name the conflict target, so you must know which unique constraint you are resolving. MySQL's version infers it, which is convenient but means the statement's behaviour depends on the schema's indexes. MERGE is the most powerful and the most verbose, and it is the only one that can handle a source with several rows per target key.

Two behaviours to know about. The update branch sees the existing row through the table's own name and the proposed row through the special excluded alias, so SET qty = inventory.qty + excluded.qty accumulates rather than replaces. And an ON CONFLICT DO NOTHING silently skips the conflicting row, which is exactly right for idempotent ingestion and exactly wrong if you expected to be told that something was skipped — so count the skips if they matter.

The standard ON CONFLICT form names the conflict target and gives the update branch access to both the stored row and the proposed row.

CREATE TABLE inventory (
  sku TEXT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  qty INTEGER NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- First shipment: no such SKU, so a row is inserted with qty 10.
INSERT INTO inventory (sku, name, qty)
VALUES ('KB-01', 'Mechanical Keyboard', 10)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + excluded.qty,
      updated_at = datetime('now');

-- Second shipment of the same SKU: the row exists, so qty accumulates to 25.
INSERT INTO inventory (sku, name, qty)
VALUES ('KB-01', 'Mechanical Keyboard', 15)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + excluded.qty,
      updated_at = datetime('now');

SELECT sku, name, qty FROM inventory;

One row comes back: KB-01, Mechanical Keyboard, quantity 25. The first statement inserted the row; the second found the conflict and added to the stored quantity rather than replacing it.

The two identifiers in the SET clause are the whole trick. excluded.qty is the value that was proposed for insertion — 15 on the second run. inventory.qty is the value already stored — 10. Adding them gives 25. If you write SET qty = excluded.qty instead, you get last-writer-wins semantics and the running total is lost.

Two dialect differences to state in an interview. MySQL has no excluded pseudo-table, so the same statement is written ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), which reads confusingly because the bare qty refers to the existing row. SQL Server has neither: the equivalent is a MERGE statement with a MATCHED branch and a NOT MATCHED branch.

One more design decision worth raising. The conflict target must match a unique index. If your table has no unique constraint on the column you are matching on, ON CONFLICT has nothing to fire on and the statement simply inserts, producing duplicates. A primary key on the business identifier is the usual answer, and it is worth adding one deliberately if the natural key is not the surrogate id.

When the incoming row is a duplicate and you are happy to throw it away. INSERT OR IGNORE — the SQLite and MySQL spellings of the same idea — attempts the insert and silently skips it if any constraint would be violated. The full upsert, by contrast, updates the existing row when it finds one.

The deciding question is what the duplicate means in your data. If the second copy carries no new information — a retried webhook, a message redelivered by a queue, a row that was already imported — then ignoring it is correct and cheap. If it carries newer information and you want that newer information to win, you need an update, not an ignore.

There is a second reason to prefer the ignore form: it is cheaper and simpler. DO NOTHING has no SET clause to evaluate, no excluded references to reason about, and fewer ways to go wrong. For high-volume idempotent ingestion — metrics events, log lines, a CDC stream replayed after a restart — it is usually the right tool.

Two cautions. The word "silently" matters: a row that was skipped because of a NOT NULL violation or a CHECK constraint is skipped just as quietly as one skipped because of a duplicate key, so if you care about distinguishing those cases, catch the error instead and inspect it. And the ignore form does not help when the duplicate has a different key — two rows with the same email but different ids still both get inserted, because no unique constraint was violated.

A reasonable rule for an interview: use the ignore form when re-running the same input must not change the state, and use the upsert form when the incoming row is a newer version of something you already have.