An upsert is a single statement that inserts a row if it is not there and updates it if it is. It exists because the "check then insert" pattern is a race: two sessions can both check, both find nothing, and both insert. The upsert moves the decision inside the database, where it is atomic.
How do upserts insert or update in one step?
The syntax differs by engine, but the idea is identical: attempt the insert, and when it collides with an existing row, apply an update instead. SQLite and PostgreSQL express it with ON CONFLICT, MySQL with ON DUPLICATE KEY UPDATE, SQL Server and Oracle with MERGE.
What does an upsert that updates look like?
Harriet Vance has moved from Bristol to Cardiff. The row already exists, so the insert is not the right operation — the row needs updating, and the update should not require a separate round trip:
BEGIN;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (3, '[email protected]', 'Harriet Vance', 'Cardiff', '2025-01-19')
ON CONFLICT (holder_id) DO UPDATE SET city = excluded.city;
SELECT holder_id, full_name, city FROM ticket_holders WHERE holder_id = 3;
holder_id full_name city
--------- ------------- -------
3 Harriet Vance Cardiff
The word excluded is the key to the syntax: inside the DO UPDATE clause it refers to the row that was proposed for insertion, while the table name refers to the row that already exists. Comparing the two — excluded.city <> ticket_holders.city — is how you write a conditional upsert.
How do you make an upsert only fire when something changed?
That is what the WHERE clause on the DO UPDATE is for. Without it, the update runs every time the statement executes, even when the incoming values are identical, and every row's modification timestamp churns for no reason:
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (3, '[email protected]', 'Harriet Vance', 'Cardiff', '2025-01-19')
ON CONFLICT (holder_id) DO UPDATE SET city = excluded.city WHERE excluded.city <> ticket_holders.city;
SELECT holder_id, city FROM ticket_holders WHERE holder_id = 3;
holder_id city
--------- -------
3 Cardiff
The city is still Cardiff, and the statement did nothing — because the incoming city matches the stored one. The same WHERE clause idiom on the DO NOTHING form is how you express "insert unless it already exists".
What are the other two forms?
DO NOTHING leaves the existing row alone and discards the incoming one — the right behaviour for a feed of events you do not want to replay:
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (3, '[email protected]', 'Someone Else', 'Bristol', '2026-01-01')
ON CONFLICT (holder_id) DO NOTHING;
SELECT holder_id, email, full_name FROM ticket_holders WHERE holder_id = 3;
holder_id email full_name
--------- ------------------------------- -------------
3 [email protected] Harriet Vance
The attempted insert was discarded and the stored row is untouched — the email did not change to someone.else. SQLite's own INSERT OR REPLACE spelling takes the opposite approach: it deletes the conflicting row and inserts the new one in its place. The result looks right —
INSERT OR REPLACE INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (5, '[email protected]', 'Rosa Pires', 'Bristol', '2025-05-23');
SELECT holder_id, full_name, city FROM ticket_holders WHERE holder_id = 5;
holder_id full_name city
--------- ---------- --------
5 Rosa Pires Bristol
— but it is a delete followed by an insert, not an update. Any row referencing that holder briefly disappears, triggers fire differently, and a new internal identity may be assigned. When the difference matters, use ON CONFLICT DO UPDATE.
The same statement also creates rows that do not exist yet, which is the point of the whole pattern:
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (7, '[email protected]', 'Newcomer', 'Cardiff', '2026-10-28')
ON CONFLICT (holder_id) DO UPDATE SET email = excluded.email;
SELECT COUNT(*) AS holders_now FROM ticket_holders;
ROLLBACK;
holders_now
7
Seven holders now, one more than before. The same statement inserted holder 7 because no row existed, and would have updated holder 7 had one. One statement, both outcomes, no race.
That ROLLBACK puts the demonstration rows away again, so that every exercise in this module starts from the same six holders — transactions are the subject of lesson 36.
What about MERGE?
MERGE is the SQL standard's answer and the spelling used by SQL Server and Oracle, with PostgreSQL adding it in version 15. It handles the same problem with a more explicit structure — a source query, a target table, and a WHEN MATCHED / WHEN NOT MATCHED clause pair:
MERGE INTO ticket_holders AS target
USING (SELECT 3 AS holder_id, 'Cardiff' AS city) AS source
ON target.holder_id = source.holder_id
WHEN MATCHED THEN UPDATE SET target.city = source.city
WHEN NOT MATCHED THEN INSERT (holder_id, city) VALUES (source.holder_id, source.city);
That statement is the SQL Server and Oracle spelling, and it produces no output here because SQLite does not understand MERGE at all — the block is reference syntax, not a runnable example. The mental model is identical to ON CONFLICT: one statement, one atomic decision per row, no check-then-act race. PostgreSQL's ON CONFLICT and SQLite's ON CONFLICT are the modern equivalents for the engines you are most likely to meet first.
Quick reference
- An upsert inserts when the key is new and updates when the key already exists, atomically.
- excluded refers to the proposed row; the table name refers to the stored row.
- A WHERE clause on DO UPDATE makes the update fire only when values actually differ.
- DO NOTHING discards the incoming row; INSERT OR REPLACE deletes and re-inserts instead.
- MERGE is the SQL standard spelling used by SQL Server, Oracle and PostgreSQL 15.
Upsert drill: write an upsert that keeps a ticket holder's city in step with the city on their most recent ticket — inserting a holder row when they are new — and explain in one sentence why an upsert is safer than a SELECT followed by an INSERT or UPDATE in application code.