SQL MERGE and UPSERT: INSERT or UPDATE in One Statement

"Insert the row if it's new, update it if it already exists" is one of the most common patterns in data loading — and doing it as a SELECT-then-INSERT-or-UPDATE in application code is both slow and race-prone. Every major database now offers a single-statement answer: MERGE in the SQL standard, INSERT ... ON CONFLICT in PostgreSQL, and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

How does the MERGE statement work?

MERGE compares a target table against a source (a table, view, or subquery) on a join condition, then applies different actions depending on whether each source row matched: update or delete on match, insert on no match. It reads like a rulebook for synchronizing two tables.

-- SQL Server / Oracle / PostgreSQL 15+
MERGE INTO customers AS t
USING staging_customers AS s
    ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET
        email      = s.email,
        updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (customer_id, email, updated_at)
    VALUES (s.customer_id, s.email, s.updated_at);

SQL Server additionally supports WHEN NOT MATCHED BY SOURCE THEN DELETE, which turns MERGE into a full table sync: rows missing from the source get removed from the target. Use that clause with a supporting filter, or a bad staging load can wipe your table.

What is the PostgreSQL upsert syntax?

PostgreSQL's native upsert is INSERT ... ON CONFLICT ... DO UPDATE, available since 9.5. You name the unique constraint (or its columns), and the special EXCLUDED pseudo-table gives the UPDATE access to the values you tried to insert. Unlike MERGE, it's guaranteed race-free under concurrency.

-- PostgreSQL
INSERT INTO customers (customer_id, email, updated_at)
VALUES (42, '[email protected]', now())
ON CONFLICT (customer_id)
DO UPDATE SET
    email      = EXCLUDED.email,
    updated_at = EXCLUDED.updated_at
WHERE customers.updated_at < EXCLUDED.updated_at;  -- optional guard

-- Or ignore duplicates entirely:
INSERT INTO customers (customer_id, email)
VALUES (42, '[email protected]')
ON CONFLICT (customer_id) DO NOTHING;

The optional WHERE on DO UPDATE is a great idempotency guard: only overwrite when the incoming row is newer, so replaying an old file can't regress data.

How do I upsert in MySQL?

MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. It fires when the insert would violate any unique index — you can't name which key, which matters on tables with several unique constraints. Since MySQL 8.0.19 you alias the new row instead of using the deprecated VALUES() function.

-- MySQL 8.0.19+
INSERT INTO customers (customer_id, email, updated_at)
VALUES (42, '[email protected]', NOW()) AS new_row
ON DUPLICATE KEY UPDATE
    email      = new_row.email,
    updated_at = new_row.updated_at;

-- Older MySQL: use VALUES(email), VALUES(updated_at)

Watch the affected-rows count: MySQL reports 1 for an insert, 2 for an update, and 0 when the update changed nothing — handy for load metrics, confusing if you don't expect it.

How do I build an idempotent load with upserts?

The classic warehouse pattern: land raw data in a staging table, deduplicate it, then upsert into the target keyed on the business key. Because upserts converge to the same final state, re-running the whole load after a failure is safe — no half-applied batches, no duplicate rows.

-- Step 1: land the batch
TRUNCATE staging_customers;
COPY staging_customers FROM '/loads/customers_2026_08_07.csv' CSV HEADER;

-- Step 2: dedupe the batch (keep latest per key)
-- Step 3: upsert into the target
INSERT INTO customers (customer_id, email, updated_at)
SELECT DISTINCT ON (customer_id)
       customer_id, email, updated_at
FROM staging_customers
ORDER BY customer_id, updated_at DESC
ON CONFLICT (customer_id)
DO UPDATE SET email = EXCLUDED.email,
              updated_at = EXCLUDED.updated_at;

Deduplicating the source first is not optional: every engine's upsert errors (or behaves unpredictably) if a single statement tries to touch the same target row twice. For updating a target from another table without inserting, see UPDATE with JOIN.

What are the known hazards of MERGE?

MERGE is powerful but has sharp edges worth knowing. First, duplicate source keys: if two source rows match one target row, the statement fails (Oracle, PostgreSQL) or behaves nondeterministically (older SQL Server). Second, concurrency: MERGE is not atomic-by-magic — two concurrent MERGEs can both see "not matched" and race to insert, causing unique-key violations; PostgreSQL's ON CONFLICT avoids this, and SQL Server needs WITH (HOLDLOCK) on the target. Third, SQL Server's MERGE has a long history of bugs and subtle interactions with triggers and indexed views — many teams ban it in favor of separate UPDATE + INSERT inside a transaction.

-- SQL Server: make MERGE concurrency-safe
MERGE INTO customers WITH (HOLDLOCK) AS t
USING staging_customers AS s
    ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET email = s.email
WHEN NOT MATCHED THEN INSERT (customer_id, email)
                      VALUES (s.customer_id, s.email);