MERGE and UPSERT

A plain INSERT fails the moment a row already exists. Real systems — syncing a feed, counting events, replaying a queue — need writes that say "insert it, or update it if it's already there." This lesson covers the three ways to do that: Postgres ON CONFLICT , MySQL ON DUPLICATE KEY UPDATE , and the SQL-standard MERGE . You'll learn the EXCLUDED pseudo-row, why a unique constraint is mandatory, and how upserts make your writes idempotent and retry-safe.

Learn MERGE and UPSERT in our free SQL course — an interactive lesson with worked examples, a practice exercise and a quick reference.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Examples run against this tiny inventory table. The key detail: sku has a UNIQUE constraint — that's what makes conflict detection possible.

When we re-load sku A-100, we don't want an error — we want its stock updated. That's the whole point of an upsert.

The first time you insert a row it works. The second time — same key — the database rejects it: duplicate key value violates unique constraint . That's correct behaviour, but it's painful for any job that runs repeatedly: a nightly product sync, an event counter, a replayed message. You'd have to "check if it exists, then INSERT or UPDATE" — two round trips and a race condition. An upsert does it in one atomic statement.

Think of saving a contact on your phone. You don't ask "does this person exist yet?" — you just hit save. If they're new, a contact is created; if they're already there, the details get updated. An upsert is that "just save it" button for a database row.

Postgres's upsert is an ordinary INSERT with an ON CONFLICT (column) DO UPDATE tail. If inserting would violate the named unique constraint, Postgres switches to updating the existing row instead. The magic word is EXCLUDED : a pseudo-row holding the values you tried to insert, so SET stock = EXCLUDED.stock applies the new value.

ON CONFLICT (sku) names the unique column(s) to watch. There must be a UNIQUE or PRIMARY KEY constraint (or unique index) on exactly those columns — otherwise Postgres has no conflict to detect and raises an error.

Sometimes you don't want to change an existing row at all — you just want to add it if it isn't there . ON CONFLICT (sku) DO NOTHING makes the insert a silent no-op on conflict: no error, no change. It's perfect for seed/reference data and "register this once" inserts.

Fill in the conflict target column and the pseudo-row name so the statement inserts a new product or updates the stock of an existing one. The expected behaviour is in the comments.

MySQL spells the same idea differently. INSERT ... ON DUPLICATE KEY UPDATE triggers the update branch when the insert would collide with any unique or primary key on the table. Where Postgres uses EXCLUDED.col , MySQL uses VALUES(col) to reference the value from the insert list.

VALUES(col) is deprecated in MySQL 8.0.20+. The modern form uses a row alias: INSERT ... AS new ON DUPLICATE KEY UPDATE stock = new.stock . Both still work today.

MERGE INTO is the most expressive form, and it's the actual SQL standard. You give it a target table, a source (a table or query), and a join condition. Then you describe what to do in each case: WHEN MATCHED THEN UPDATE for rows that already exist, and WHEN NOT MATCHED THEN INSERT for rows that are new. Some engines also allow WHEN MATCHED THEN DELETE .

MERGE ships in SQL Server and Oracle (for years), and Postgres added it in version 15 . MySQL and older Postgres don't have it — there, use the upsert forms above instead.

Two blanks: the keyword for rows that already exist, and the action for brand new rows. Read the comments to confirm both branches.

Upserts shine wherever the same key shows up again and again. A running counter increments an existing value or starts it at 1. A nightly sync reloads a product feed without caring which rows are new. A replayed message queue can re-apply the same write safely. The counter pattern below references both page_views.views (the stored value) and EXCLUDED.views (the delta) on a composite unique key.

Q: ON CONFLICT vs MERGE — which should I use?

On Postgres, ON CONFLICT is the simplest and most battle-tested for single-table upserts. Reach for MERGE when you're syncing one table from another (a staging/source table) and want update, insert, and possibly delete in one statement — and your engine supports it.

In a Postgres DO UPDATE , EXCLUDED is a virtual row holding the values from your INSERT that were "excluded" because of the conflict. Use EXCLUDED.col to apply the incoming value, and the table name to read the stored one.

Every upsert decides "does this already exist?" by checking a unique or primary key. With no such constraint there's no conflict to detect, so ON CONFLICT errors and ON DUPLICATE KEY never fires. The constraint is the whole mechanism.

An operation is idempotent if running it twice leaves the same end state as running it once. Upserts are idempotent (re-applying the same data updates to the same values), which makes retries, replays, and at-least-once delivery safe.

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a Postgres playground to confirm it's idempotent.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary