MERGE and UPSERT

Reviewed & published by Brayan K

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.

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.

What You'll Learn

• Write a Postgres upsert with INSERT ... ON CONFLICT DO UPDATE
• Use the EXCLUDED pseudo-row to apply the proposed values
• Skip silently on conflict with DO NOTHING
• Write MySQL's INSERT ... ON DUPLICATE KEY UPDATE
• Sync two tables with the standard MERGE statement
• Build idempotent, retry-safe writes for feeds and counters

Our Sample Table: inventory

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.

1. Why a Plain INSERT Isn't Enough

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.

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
-- sku is UNIQUE on purpose: that constraint is what makes a plain INSERT fail
-- on a re-run, which is the problem upserts exist to solve.
CREATE TABLE inventory (
    sku   TEXT PRIMARY KEY,
    name  TEXT,
    stock INTEGER
);

-- A-100 is deliberately NOT here: the very next statement inserts it, and the
-- lesson's point is that running that a second time is what fails.
INSERT INTO inventory (sku, name, stock) VALUES
    ('A-200', 'Grommet', 30),
    ('A-300', 'Flange',  75);

-- A hit counter, for the "upsert as increment" example further down.
-- The key is (page, day) together, because the counter is per page PER DAY —
-- that composite is what the ON CONFLICT clause below has to name.
CREATE TABLE page_views (
    page  TEXT,
    day   DATE,
    views INTEGER,
    PRIMARY KEY (page, day)
);
INSERT INTO page_views (page, day, views) VALUES
    ('/pricing', '2026-03-01', 12),
    ('/docs',    '2026-03-01', 47);

-- The problem: a plain INSERT fails when the key already exists.
-- Say inventory has a UNIQUE (sku). We try to (re)load a product:
INSERT INTO inventory (sku, name, stock)
VALUES ('A-100', 'Widget', 50);

-- Run it once: fine. Run it a SECOND time with the same sku and you get:
--   ERROR: duplicate key value violates unique constraint
-- We don't want an error on the re-run. We want "insert it, or if it's
-- already there, just update the stock". That is an UPSERT.

2. Postgres: ON CONFLICT DO UPDATE

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.

💡 The conflict target is required

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.

-- Postgres UPSERT: INSERT ... ON CONFLICT (col) DO UPDATE.
-- Needs a UNIQUE or PRIMARY KEY constraint on the conflict target (sku).
INSERT INTO inventory (sku, name, stock)
VALUES ('A-100', 'Widget', 75)
ON CONFLICT (sku) DO UPDATE
SET stock = EXCLUDED.stock,
    name  = EXCLUDED.name;

-- EXCLUDED is a pseudo-row holding the values you TRIED to insert.
-- So EXCLUDED.stock is 75 here. On first run the row is inserted;
-- on every later run with the same sku, stock is updated to the new
-- value. Same statement, no error, correct result either way.

3. DO NOTHING — Insert Only If Absent

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.

-- "Insert only if absent" — skip silently on conflict.
INSERT INTO inventory (sku, name, stock)
VALUES ('A-100', 'Widget', 50)
ON CONFLICT (sku) DO NOTHING;

-- If sku A-100 already exists, this does NOTHING (no error, no change).
-- Great for seed data, idempotent inserts, or "register this once".
-- You can also add a WHERE to update conditionally:
--   ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock
--   WHERE inventory.stock < EXCLUDED.stock;   -- only grow, never shrink

Your Turn: write an upsert

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.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: insert a product, or update its stock if the sku already exists.

INSERT INTO inventory (sku, name, stock)
VALUES ('B-200', 'Gadget', 30)
ON CONFLICT (___) DO UPDATE          -- 👉 the unique column to conflict on
SET stock = ___.stock;               -- 👉 the pseudo-row with the new value

-- ✅ Expected: first run inserts the B-200 row; a second run with a
--    different stock value updates the existing row instead of erroring.

4. MySQL: ON DUPLICATE KEY UPDATE

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.

-- MySQL UPSERT: INSERT ... ON DUPLICATE KEY UPDATE.
-- Keyed off ANY unique or primary key on the table.
INSERT INTO inventory (sku, name, stock)
VALUES ('A-100', 'Widget', 75)
ON DUPLICATE KEY UPDATE
    stock = VALUES(stock),
    name  = VALUES(name);

-- MySQL's VALUES(col) plays the same role Postgres's EXCLUDED.col does:
-- it refers to the value from the INSERT list. (Newer MySQL prefers a
-- row alias: ... AS new ON DUPLICATE KEY UPDATE stock = new.stock.)

5. The SQL-Standard MERGE

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.

-- The SQL-standard MERGE: one statement, both branches.
-- (SQL Server, Oracle, and Postgres 15+.) We sync a target table
-- 'inventory' from a 'staging' table of incoming rows.
MERGE INTO inventory AS t
USING staging AS s
   ON t.sku = s.sku
WHEN MATCHED THEN
    UPDATE SET stock = s.stock, name = s.name
WHEN NOT MATCHED THEN
    INSERT (sku, name, stock)
    VALUES (s.sku, s.name, s.stock);

-- WHEN MATCHED handles rows that already exist (update them).
-- WHEN NOT MATCHED handles new rows from the source (insert them).
-- It's the most expressive form: some engines also allow
-- WHEN MATCHED THEN DELETE for rows that should be removed.

Your Turn: finish the MERGE

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

-- 🎯 YOUR TURN — finish the MERGE so it both updates and inserts.

MERGE INTO inventory AS t
USING staging AS s
   ON t.sku = s.sku
WHEN ___ THEN                         -- 👉 the branch for rows that exist
    UPDATE SET stock = s.stock
WHEN NOT MATCHED THEN
    ___ (sku, name, stock)            -- 👉 the action for brand new rows
    VALUES (s.sku, s.name, s.stock);

-- ✅ Expected: existing skus get their stock updated; skus only in
--    staging get inserted as new rows. One pass, both outcomes.

6. Use Cases: Counters, Sync, and Feeds

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.

-- A classic upsert use case: a running counter / aggregate.
-- "Increment today's view count for this page; create it if missing."
INSERT INTO page_views (page, day, views)
VALUES ('/home', CURRENT_DATE, 1)
ON CONFLICT (page, day) DO UPDATE
SET views = page_views.views + EXCLUDED.views;

-- First hit of the day inserts views = 1. Every later hit adds 1 to the
-- EXISTING value. Note we reference page_views.views (the stored value)
-- and EXCLUDED.views (the +1 we're adding). The conflict target is the
-- COMPOSITE unique key (page, day).

Common Errors (and the fix)

• "there is no unique or exclusion constraint matching the ON CONFLICT specification": you named columns in ON CONFLICT (...) that have no UNIQUE/PRIMARY KEY constraint. Add the constraint, or point the conflict target at the columns that do.
• Referencing the wrong row in DO UPDATE: EXCLUDED.col is the value you tried to insert; the bare table name (inventory.col) is the value already stored. Mixing them up is the usual cause of a counter that resets instead of incrementing.
• Using EXCLUDED in MySQL (or VALUES() in Postgres): they're engine-specific. Postgres = EXCLUDED.col; MySQL = VALUES(col) or a row alias. Don't cross them.
• MERGE on an unsupported engine: MERGE needs SQL Server, Oracle, or Postgres 15+. On MySQL or older Postgres you'll get a syntax error — use the upsert forms instead.
• Race conditions without a unique key: a "SELECT then INSERT/UPDATE" in app code can let two concurrent sessions both insert. A single upsert statement against a unique constraint is atomic and avoids the race — that's a big reason to prefer it.

Frequently Asked Questions

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.

Q: What exactly is the EXCLUDED row?

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.

Q: Why do I need a unique constraint?

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.

Q: What does idempotent mean here?

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.

Mini-Challenge: Idempotent Feed Loader

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.

-- 🎯 MINI-CHALLENGE: an idempotent feed loader
-- Using ONLY what this lesson covered (ON CONFLICT ... DO UPDATE):
--   1. INSERT a row into prices (symbol, price) with VALUES ('AAPL', 195)
--   2. The table has UNIQUE (symbol)
--   3. On conflict, UPDATE price to the incoming value (EXCLUDED.price)
--   4. Make it so re-running the exact same statement changes nothing
--      the second time (that's idempotency)
--
-- ✅ Expected: run #1 inserts AAPL at 195; run #2 (same data) updates
--    AAPL to 195 again -> final state identical, no error either run.

-- your statement here

🎉 Lesson Complete

• ✅ A plain INSERT errors on a duplicate key; an upsert handles it gracefully
• ✅ Postgres: INSERT ... ON CONFLICT (col) DO UPDATE with the EXCLUDED pseudo-row
• ✅ DO NOTHING inserts only if absent; a WHERE makes the update conditional
• ✅ MySQL: INSERT ... ON DUPLICATE KEY UPDATE using VALUES(col) or a row alias
• ✅ MERGE INTO syncs target from source with WHEN MATCHED / WHEN NOT MATCHED
• ✅ Every form needs a unique constraint; upserts are atomic and idempotent
• ✅ Next: storing and searching embeddings for semantic search

Practice quiz

Continue this course

• Previous: Streaming & Real-Time SQL
• Next: Vector & Similarity Search — Store embeddings and run k-nearest-neighbor search with pgvector distance operators
• Quick reference: SQL cheat sheet › Modifying Data

Related lessons

• INSERT / UPDATE
• UNIQUE & PRIMARY KEY constraints