An upsert is a single statement that inserts a row when it is not there and updates it when 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.
The previous lesson in this module covered "What Is the Difference Between DELETE and TRUNCATE?", and the next lesson in this module is about "What Do Transactions Guarantee With BEGIN and COMMIT?".
How do the upserts insert or update in one step?
The syntax differs by the engine, but the idea is identical. You attempt the insert, and when it collides with an existing row, you apply an update instead. SQLite and PostgreSQL express it with the ON CONFLICT, MySQL with the ON DUPLICATE KEY UPDATE, and the SQL Server and Oracle with the 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 the update, and the update should not need 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 CardiffThe word excluded is the key to the syntax. Inside the DO UPDATE clause it refers to the row that was proposed for the insertion, while the name of the table refers to the row that already exists.
Comparing the two of them, which is excluded.city <> ticket_holders.city, is how you write a conditional upsert.
How do you make an upsert fire only when something changed?
That is what the WHERE clause on the DO UPDATE is for. Without it, the update runs every time that the statement executes, even when the incoming values are identical, and the modification timestamp of every row 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 CardiffThe 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?
The DO NOTHING leaves the existing row alone and discards the incoming one. That is the right behaviour for a feed of events that 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 VanceThe attempted insert was discarded, and the stored row is untouched. The email did not change to someone.else.
SQLite has its own spelling of this, the INSERT OR REPLACE, and it 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 BristolBut it is a delete followed by an insert, and it is not an update. Any row that references that holder briefly disappears, the triggers fire differently, and a new internal identity may be assigned. When the difference matters, you use the ON CONFLICT DO UPDATE.
The same statement also creates the rows that do not exist yet, and that 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
-----------
7There are seven holders now, which is one more than before. The same statement inserted the holder 7 because no row existed, and it would have updated the holder 7 had one existed. One statement and both outcomes, with 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 the MERGE?
The MERGE is the answer of the SQL standard, and it is the spelling that the SQL Server and Oracle use, with PostgreSQL adding it in version 15.
It handles the same problem with a more explicit structure. There is a source query, there is a target table, and there is a WHEN MATCHED and 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 spelling of the SQL Server and of Oracle, and it produces no output here because SQLite does not understand the MERGE at all. The block is the reference syntax and not a runnable example.
The mental model is identical to the ON CONFLICT. There is one statement, there is one atomic decision for each row, and there is no check-then-act race. The ON CONFLICT of PostgreSQL and the ON CONFLICT of SQLite are the modern equivalents for the engines that you are most likely to meet first.
Quick reference
- An upsert inserts when the key is new and it updates when the key already exists, and it does it atomically.
- The word excluded refers to the proposed row, and the name of the table refers to the stored row.
- A WHERE clause on the DO UPDATE makes the update fire only when the values actually differ.
- The DO NOTHING discards the incoming row, while the INSERT OR REPLACE deletes and re-inserts instead.
- The MERGE is the spelling of the SQL standard that the SQL Server, Oracle and PostgreSQL 15 use.
Upsert drill: you write an upsert that keeps the city of a ticket holder in step with the city on their most recent ticket, and that inserts a holder row when they are new. Then you explain in one sentence why an upsert is safer than a SELECT that is followed by an INSERT or an UPDATE in the application code.