SQL UPSERT Explained: ON CONFLICT vs ON DUPLICATE KEY vs MERGE

Why upsert exists

The naive way to "insert this row, or update it if it already exists" is two separate steps: a SELECT to check for an existing row, then an INSERT or UPDATE depending on what was found. The problem is the gap between those steps — under concurrent load, another transaction can insert the same row in the time between your SELECT and your subsequent write, and you either get a duplicate-key error or silently lose an update. Upsert collapses the check and the write into a single atomic statement, closing that window entirely.

PostgreSQL & SQLite: INSERT ... ON CONFLICT

INSERT INTO inventory (sku, quantity)
VALUES ('ABC123', 50)
ON CONFLICT (sku)
DO UPDATE SET quantity = inventory.quantity + 50;

The ON CONFLICT (sku) clause names the unique constraint or column to check. DO UPDATE SET can reference the row being inserted via EXCLUDED, or the existing row directly by table name, as shown here to increment rather than overwrite. ON CONFLICT (sku) DO NOTHING is the alternative when you just want to skip silently on a duplicate instead of updating.

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO inventory (sku, quantity)
VALUES ('ABC123', 50)
ON DUPLICATE KEY UPDATE quantity = quantity + 50;

MySQL doesn't name the conflicting column explicitly in the statement — it checks any unique index or primary key on the table and triggers the update clause if any of them collide, which is slightly less precise than PostgreSQL's explicit ON CONFLICT (column) targeting.

SQL Server & Oracle: MERGE

MERGE is the standard SQL approach and the most flexible of the three — it can insert, update, and delete in one statement, matching against an entire second table or result set rather than just a single row of literal values:

MERGE INTO inventory AS target
USING (SELECT 'ABC123' AS sku, 50 AS quantity) AS source
ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, quantity) VALUES (source.sku, source.quantity);

MERGE's extra power — matching against a whole derived table instead of one row — is exactly why it's the right tool for bulk upserts (syncing an entire staging table into a target table in one statement), where ON CONFLICT and ON DUPLICATE KEY UPDATE are typically used for single-row upserts in application code.

The unique constraint requirement

All three approaches depend on the same prerequisite: a unique constraint or primary key on the column or columns being checked for a conflict. Without one, the database has no mechanism to detect that a "duplicate" is even happening — the INSERT will simply succeed and add a second row, silently defeating the entire purpose of the upsert.

-- Required before ON CONFLICT (sku) can work at all
ALTER TABLE inventory ADD CONSTRAINT uq_inventory_sku UNIQUE (sku);

Common upsert mistakes

• No unique constraint on the conflict column. The statement runs without error but just inserts duplicates, because there's nothing for the database to conflict against.
• Using DO NOTHING when an update was actually intended. ON CONFLICT DO NOTHING silently discards the incoming row's new values on a conflict — easy to reach for by habit when DO UPDATE was the actual goal.
• Overwriting instead of incrementing. SET quantity = 50 replaces the existing value; SET quantity = inventory.quantity + 50 adds to it — mixing these up is a common source of "my stock counts are wrong" bugs.
• Assuming MERGE is atomic against race conditions the same way ON CONFLICT is. Some database versions have had documented concurrency edge cases with MERGE under high write contention — check your specific database's documentation before relying on it as a strict concurrency guarantee.