MERGE (Upsert)

A very common problem: you have an incoming row of data, and you don’t know whether a matching row already exists. If it does, you want to update it; if it doesn’t, you want to insert it. Doing this as two separate statements is awkward and prone to race conditions. This combined operation is usually called an upsert (update + insert), and SQL gives you a few ways to express it.

The standard SQL MERGE statement

MERGE (part of the SQL standard, and supported by SQL Server, Oracle, and recent PostgreSQL versions) lets you compare a source of new data against a target table and decide, per row, whether to update, insert, or even delete:

MERGE syntax

SQL

MERGE INTO inventory AS target
USING incoming_stock AS source
  ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET target.quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity)
  VALUES (source.product_id, source.quantity);

Read it as: match rows from incoming_stock against inventory by product_id. Where a match is found, add to the existing quantity. Where no match is found, insert a brand-new row.

PostgreSQL: INSERT ... ON CONFLICT

PostgreSQL popularized a more compact upsert syntax that doesn’t require a separate source table — it works directly off a normal INSERT, reacting to a unique constraint violation:

PostgreSQL upsert

SQL

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

EXCLUDED refers to the row that was proposed for insertion — the values you passed into VALUES. If you instead want to silently skip the insert when a conflict occurs, use DO NOTHING:

Ignore on conflict

SQL

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT (product_id) DO NOTHING;

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

MySQL achieves the same result with its own syntax, also triggered by a unique or primary key conflict:

MySQL upsert

SQL

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

Dialect summary

SQL Server and Oracle favor the full MERGE statement; PostgreSQL favors INSERT ... ON CONFLICT; MySQL favors INSERT ... ON DUPLICATE KEY UPDATE. All three achieve the same upsert outcome — pick the syntax your database supports.

Why not just SELECT, then decide?

Checking with a SELECT first and then choosing INSERT or UPDATE requires two round trips and is vulnerable to a race condition if another connection inserts the same row in between
MERGE / ON CONFLICT / ON DUPLICATE KEY UPDATE perform the check-and-act atomically at the database level, avoiding that race entirely
They also result in simpler application code — one statement instead of branching logic

Tip

An upsert requires a unique constraint or primary key on the column(s) you are matching against (like product_id above) — that constraint is what the database uses to detect a “conflict” or a “duplicate key” in the first place.