Free SQL queries lessonsSQL queries · Stage 5 · Changing data · Step 4

Insert or update

Add a row, or update it when it already exists, in one statement. About 9 minutes.

Insert, or update if it is there

A stock table has one row per item. A delivery arrives: if the item has a row, add to its quantity. If not, create the row. With plain statements you'd check first, then insert or update, and two deliveries at the same moment can both decide "no row yet".

An upsert does it in one statement. ON CONFLICT names the unique column. DO UPDATE says what to change when the row exists, and excluded.qty is the value you tried to insert.

ON CONFLICT (key) DO UPDATE: insert, or update when the key exists.

In code

INSERT INTO stock (item, qty)
VALUES ('Pen', 12)
ON CONFLICT(item)
DO UPDATE SET qty = qty + excluded.qty;

A delivery of 5 pens and 3 bags

stock

item	qty
Pen	10
Book	4

Pen already has a row, so the insert conflicts. DO UPDATE adds: 10 + 5 = 15.

Move 1 of 3

IGNORE, REPLACE and a condition

INSERT OR IGNORE quietly skips a row that would break a unique rule. Useful for "add if missing", and it never changes the existing row.

INSERT OR REPLACE looks similar, but it deletes the old row and inserts a new one, so any columns you didn't list go back to their defaults. Prefer ON CONFLICT DO UPDATE. It can also carry a WHERE, to update only when the new data is better, for example only when the new date is later.

OR REPLACE: deletes the old row, so unlisted columns reset.

In code

INSERT INTO prices (item, price, day)
VALUES ('Pen', 12, 30)
ON CONFLICT(item) DO UPDATE
SET price = excluded.price,
    day = excluded.day
WHERE excluded.day > prices.day;

Quick check

stock has Pen with qty 10. You run INSERT … VALUES ('Pen', 5) ON CONFLICT(item) DO UPDATE SET qty = qty + excluded.qty. What is Pen's qty?

Show the answer

15. qty is the stored 10, excluded.qty is the 5 you tried to insert: 10 + 5.

Your problem

Stock arrives

The stock table has one row per item and the quantity on hand. A delivery of 12 units of Pen has arrived. In one statement, add 12 to Pen's quantity, or create the Pen row with a quantity of 12 if it is not in the table yet.

Tables

stock(item, qty)

Up to 2,000 items

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.