SQL Upsert Explained: INSERT ON CONFLICT, MERGE and the REPLACE Trap

A nightly job loads stock levels from a supplier file. Some products are new, and most already exist. Checking each row with a SELECT and then choosing INSERT or UPDATE is slow and, as we will see, unsafe when two jobs run at once. An SQL upsert does both in one atomic statement.

We ran the examples in SQLite 3.50, which uses the same ON CONFLICT syntax as PostgreSQL. The outputs shown are from that run. MySQL, SQL Server and MERGE examples are shown from their documentation and were not run.

What is an upsert?

“Upsert” is a blend of update and insert. You give the database a row. If no row with the same key exists, it inserts it. If one does, it updates the existing row instead of failing. The SQL standard does not use the word, and each database spells it differently, but the idea is the same everywhere.

Why not just check first?

The obvious approach is “SELECT; if found, UPDATE, else INSERT”. It has two problems. It takes two round trips per row, and it has a race condition. If two processes check at the same moment, both see “not found”, both insert, and one fails with a duplicate key error, or worse, creates a duplicate where no constraint exists. An upsert makes the check and the write a single operation, protected by the unique constraint. Our transactions and ACID guide explains why this kind of atomicity matters.

The sample table

CREATE TABLE stock (
  sku TEXT PRIMARY KEY,
  qty INT NOT NULL,
  price INT,
  updated TEXT DEFAULT 'initial'
);
INSERT INTO stock (sku, qty, price) VALUES ('PEN', 10, 20);

A plain insert of the same SKU fails, as it should:

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 5, 20);
-- UNIQUE constraint failed: stock.sku

ON CONFLICT DO UPDATE

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 5, 22)
ON CONFLICT(sku) DO UPDATE SET
  qty = qty + excluded.qty,
  price = excluded.price,
  updated = 'upsert';

INSERT INTO stock (sku, qty, price) VALUES ('INK', 4, 50)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- ('INK', 4, 50, 'initial')
-- ('PEN', 15, 22, 'upsert')

PEN already existed, so the update ran: quantity went from 10 to 15 and the price changed to 22. INK was new, so it was simply inserted. excluded is a special name for “the row I tried to insert”, so excluded.qty is 5. Without the prefix, qty means the value already in the table.

The column in ON CONFLICT(sku), called the conflict target, must match a primary key or unique constraint. When we pointed it at a column without one, SQLite refused: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint. PostgreSQL behaves the same way.

DO NOTHING

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 99, 1)
ON CONFLICT(sku) DO NOTHING;
-- PEN unchanged: ('PEN', 15, 22, 'upsert')

DO NOTHING is “insert if new, otherwise ignore”. It is ideal for idempotent jobs, such as recording that an event was processed, where running the same import twice should not change anything. It is also how many teams load reference data safely in CI pipelines and test setups.

Conditional updates

A WHERE clause on the update decides whether the existing row should change. Here we only accept a price that is higher than the current one:

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 1, 30)
ON CONFLICT(sku) DO UPDATE SET price = excluded.price
WHERE excluded.price > stock.price;    -- 30 > 22, so it updates

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 1, 5)
ON CONFLICT(sku) DO UPDATE SET price = excluded.price
WHERE excluded.price > stock.price;    -- 5 is lower, so nothing changes

-- ('PEN', 15, 30, 'upsert')

This pattern is common for syncing data from several sources: only apply an incoming record if its timestamp or version is newer than the stored one. That protects you from an old, delayed message overwriting fresh data.

RETURNING

INSERT INTO stock (sku, qty, price) VALUES ('INK', 1, 50)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty
RETURNING sku, qty;
-- ('INK', 5)

RETURNING gives back the final row in the same statement, saving a follow-up SELECT. PostgreSQL and SQLite support it; SQL Server has a similar OUTPUT clause.

The REPLACE trap

SQLite’s INSERT OR REPLACE and MySQL’s REPLACE INTO look like upserts, but they are not. On a conflict they delete the old row and insert a new one. We tried it:

INSERT OR REPLACE INTO stock (sku, qty) VALUES ('PEN', 1);
-- ('PEN', 1, None, 'initial')

We only meant to change the quantity, but the price became NULL and the updated column was reset to its default, because the whole row was replaced. In a real table, that could also mean a new auto-increment ID, lost columns and, through foreign keys with ON DELETE CASCADE, deleted child rows.

There is a quieter surprise too. Our table had an audit trigger on DELETE, and it did not fire. In SQLite, delete triggers fire for REPLACE only when recursive triggers are enabled, so the audit log silently missed the deletion.

Syntax by database

The MySQL form looks like this (not run for this article):

INSERT INTO stock (sku, qty, price) VALUES ('PEN', 5, 22) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty, price = new.price;

Older MySQL versions use VALUES(qty) instead of a row alias. MySQL applies this to any unique key on the table, not just one you name, which can surprise you on tables with several unique indexes.

MERGE

MERGE is the SQL standard’s more general statement. It compares a source with a target and can insert, update and even delete in one go. This SQL Server example was not run for this article:

MERGE INTO stock AS t
USING (VALUES ('PEN', 5, 22)) AS s (sku, qty, price)
ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET qty = t.qty + s.qty, price = s.price
WHEN NOT MATCHED THEN
  INSERT (sku, qty, price) VALUES (s.sku, s.qty, s.price);

MERGE shines when syncing a whole staging table into a main table. For a single-row upsert, though, it is wordy, and SQL Server’s MERGE needs care under concurrency; Microsoft’s guidance is to use an appropriate lock hint, such as HOLDLOCK, if two sessions may merge the same keys.

Practical tips

• Batch it. Upserting many rows in one statement or one transaction is far faster than one at a time. Our executemany test applied three rows, including a repeat of key A, and the last value for A won.
• Index the conflict target. A unique constraint creates an index automatically, which keeps the conflict check fast. See SQL indexes explained.
• Do not update every column blindly. Leave out created-at timestamps and IDs.
• Watch duplicates within one statement. PostgreSQL rejects a multi-row upsert that would update the same row twice; de-duplicate the input first.
• Test the conflict path. Run every import twice in your tests and check that the second run changes nothing it should not.

Final thoughts

Upserts replace fragile “check, then write” code with one atomic statement. Use ON CONFLICT DO UPDATE in PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE in MySQL, and MERGE for larger syncs. Most importantly, update only the columns you mean to change, and stay away from REPLACE unless you truly want the whole row rebuilt.

Frequently asked questions

Keep learning