sql · level 3 · unit 5 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
turns a clashing insert into an update.
A delivery brings 6 eggs. If eggs are already in the pantry, add to them. If not, add a new row. Checking first takes two statements. An , short for update or insert, does it in one.
Add ON CONFLICT (item) DO UPDATE SET ... to the INSERT. When a new row clashes with an existing item, that row is updated instead. means the value you tried to insert. DO NOTHING skips the row quietly.
The conflict column must be a PRIMARY KEY or UNIQUE, so the database knows what counts as a clash.
INSERT INTO stock (item, qty) VALUES ('eggs', 6), ('butter', 2)
ON CONFLICT (item) DO UPDATE SET qty = qty + excluded.qty;
SELECT item, qty FROM stock ORDER BY item;In qty + excluded.qty, plain qty is the old value already in the table.
guess first
Milk is already there, jam is new. What happens?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
INSERT INTO stock (item, qty) VALUES ('milk', 10), ('jam', 1)
ON CONFLICT (item) DO NOTHING;
SELECT item, qty FROM stock WHERE item IN ('milk', 'jam') ORDER BY item;your turn
Your turn. Record a stock-take.
A stock-take counts everything again, so a clash should replace the old amount with the new count. Fill the blanks with the conflict column and the name for the row you tried to insert.
result
Your answer appears here as a table.
fix it
The scoreboard upsert has nothing to clash with. Fix it!
result
Your answer appears here as a table.
- Read the error. What does
ON CONFLICT (player)need from the player column? - A clash only exists when the column must be unique. Here two rows for Ava are allowed.
- Make the column player TEXT PRIMARY KEY on line 1, then press run.
nailed it
no more duplicate rows. :D
- An upsert inserts new rows and updates existing ones.
excluded.qtyis the value you tried to insert.ON CONFLICTneeds aPRIMARY KEYorUNIQUEcolumn.
new words in your collection
- upsert
- ON CONFLICT
- excluded
This lesson isn’t saved. A free account remembers every lesson you finish.