UPSERT: insert or update
In short: An upsert inserts a new row, or updates or skips it if a row with that key exists; in SQLite and PostgreSQL it is written INSERT … ON CONFLICT.
Problem
The customer comes back
Georgi Petrov has moved to Sofia. He orders without logging in, and the form sends his name, email and city. The website tries to save him as a new customer:
His email is already in the database, and it is unique. The website has to decide: a new customer or an existing one. It could check with a SELECT first and then choose between INSERT and UPDATE. But that is two statements, and between them another visitor could save the same email.
The idea
ON CONFLICT … DO UPDATE
ON CONFLICT tells INSERT what to do if the row breaks a unique column. With DO UPDATE it updates the row that is already in the table:
The two rows take different paths. Georgi is there: his row keeps number 2 and the sign-up date from 2025, and only the city changes. Vesela is not there: she is inserted as customer 21.
excluded is the row that the statement tried to insert. excluded.city is the city from VALUES, and city with nothing in front is the old value in the table.
This is called an upsert, from “update” and “insert”.
More precisely, if you are curious
In PostgreSQL the statement is the same: ON CONFLICT (email) DO UPDATE SET city = EXCLUDED.city. MySQL has its own form: INSERT … ON DUPLICATE KEY UPDATE. SQL Server and Oracle use MERGE, a more general command that compares two tables and decides for each row: insert, update or delete. PostgreSQL has had MERGE since version 15. SQLite has no MERGE.
Try it
A new address
Elena Dimitrova (elena.d@example.com) has moved to Varna. The form sends her name, email, the city Varna and today's date, 2026-10-25. Save her so that only the city changes. Her sign-up date stays the old one.
Solution
INSERT INTO customers (name, email, city, joined_at)
VALUES ('Elena Dimitrova', 'elena.d@example.com', 'Varna', '2026-10-25')
ON CONFLICT (email) DO UPDATE SET city = excluded.city;INSERT INTO customers (name, email, city, joined_at) VALUES ('Elena Dimitrova', 'elena.d@example.com', 'Varna', '2026-10-25') ON CONFLICT (email) DO UPDATE SET city = excluded.city. If you also add joined_at = excluded.joined_at, Elena will look like a customer since today. The whole customers table is checked.
The idea
Stock from the supplier
A delivery arrives in the morning: 30 banitsas, 40 cucumbers and a new product, ayran. The supplier uses the shop's product numbers, and the ayran already has number 26 in the catalogue. The known products must get more stock, and the new one must appear:
The conflict is on id, the primary key. For the banitsa and the cucumbers products.stock + excluded.stock adds the delivered stock to the old one. Their added_at date stays the old one, because it is not in SET. The ayran is not there, so it is inserted with all its columns.
Here stock has products. in front, because the expression has two stocks: the one in the table and the one from the delivery.
Try it
More in the cart
Georgi adds 2 packs of butter (product 9, 3.20 €) to his new order 57. If it is there, the quantity must go up, and if it is not, a row must be added.
Solution
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (57, 9, 2, 3.2)
ON CONFLICT (order_id, product_id) DO UPDATE SET quantity = quantity + excluded.quantity;INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (57, 9, 2, 3.2) ON CONFLICT (order_id, product_id) DO UPDATE SET quantity = quantity + excluded.quantity. In order_items the key is the pair of order and product, so the conflict is on both columns. The butter was 3 and becomes 5.
See it
DO NOTHING
Sometimes nothing should change on a conflict. A list of people who want to become customers comes from a trade fair. Whoever is already a customer is skipped:
Maria stays as she was, with the city Sofia. Vesela is a new customer. Without ON CONFLICT the whole statement would have stopped at Maria, and Vesela would not have got in.
More precisely, if you are curious
SQLite also has an older form of its own: INSERT OR IGNORE does the same as DO NOTHING.
INSERT OR REPLACE looks like an upsert, but it is not. On a conflict it deletes the old row and inserts a new one. Teodora gets a new number and a new sign-up date:
For Georgi the delete does not go through at all, because his orders point to the old row:
So for an upsert use ON CONFLICT … DO UPDATE. It changes only the columns in SET.
Exercise
The delivery
The supplier sends the delivery as a table delivery: product number, name, category, price and quantity. For the lesson the table is already in the database. Run the starting SELECT to see it.
Save the delivery into products. The known products get the delivered quantity added to their stock and the supplier's price. The new ones are inserted with the date 2026-10-25.
The delivery in products:
Solution
INSERT INTO products (id, name, category_id, price, stock, added_at)
SELECT product_id, name, category_id, price, quantity, '2026-10-25'
FROM delivery
WHERE true
ON CONFLICT (id) DO UPDATE SET
stock = products.stock + excluded.stock,
price = excluded.price;INSERT INTO products (id, name, category_id, price, stock, added_at) SELECT product_id, name, category_id, price, quantity, '2026-10-25' FROM delivery WHERE true ON CONFLICT (id) DO UPDATE SET stock = products.stock + excluded.stock, price = excluded.price. The butter becomes 3.40 € with 40 in stock, the coffee has 25, and the green tea is the new product 26.
WHERE true looks pointless, but it is needed. Without it SQLite reads ON as the start of JOIN … ON and stops with an error. When the rows for an upsert come from a SELECT, add a WHERE, even if it is only WHERE true.
Test