Please change the menu board.
Everything so far has only read the tables. SELECT never changes anything. Today you learn the three words that change data: one to add a row, one to change rows, and one to remove rows.
Add, change, remove
Think of the café's menu board. You can pin up a new card, rub out and rewrite a card, or take a card down. SQL has one word for each.
INSERT INTO menu (…) VALUES (…)
Pin up a new card. The new row appears in green in the playground.
UPDATE menu SET … WHERE …
Rub out and rewrite. Changed cells appear in amber.
DELETE FROM menu WHERE …
Take the card down. Removed rows are crossed out in red.
Two lists that pair up
INSERT INTO takes two lists in brackets: first the column names, then after VALUES the new values. They pair up in order: first with first, second with second. So both lists must be the same length.
| Columns | id | item | type | price | calories |
| ↓ | ↓ | ↓ | ↓ | ↓ | |
| Values | 9 | 'Lemon Tart' | 'food' | 3.2 | 300 |
3.2.SET means “change it to”
UPDATE names the table, SET says which column gets which new value, and WHERE says which rows to change.
| item | price |
|---|---|
| Espresso | £2.50 |
| Cappuccino | £3.50 |
| Iced Latte | £4.50 |
UPDATE menu SET price = 3.8 WHERE item = 'Cappuccino'| item | price |
|---|---|
| Espresso | £2.50 |
| Cappuccino | |
| Iced Latte | £4.50 |
= means “change it to”. In WHERE, = means “is”, a test. Same symbol, different job, depending on where it is.SET price = price + 0.2 means “make the price 20p more”. The right side uses the price as it was before the change.No WHERE means every row
UPDATE menu SET price = 1 makes everything on the menu cost £1.00. DELETE FROM orders throws away every order. In a real database there is usually no Undo button, so this is the most important habit in this lesson.
SELECT * FROM menu WHERE item = 'Fruit Cup';Check that only the rows you mean come back.DELETE FROM menu WHERE item = 'Fruit Cup';Swap SELECT * for DELETE, and keep the same WHERE.Look before you change: the same WHERE that found the rows in step 1 is the one that changes them in step 2. The playground asks you to confirm whenever you leave out WHERE.
It protects the links between tables
Orders point at menu items by their number. So the database stops you from breaking those links:
DELETE FROM menu WHERE id = 2 is refused.item_id 12 is refused.orders.item_id, pointing at menu.id.Add with VALUES, change with SET, remove with FROM
INSERT INTO menu (…) VALUES (…);“Please add into the menu… these values.”UPDATE menu SET … WHERE …;“Please change the menu: set… but only where…”DELETE FROM menu WHERE …;“Please remove from the menu, but only where…”Every piece has a meaning, even the two = signs
Please press Next (or click any piece) and watch the sentence grow underneath.
Say each one out loud
Swap each word for what you say, and the code becomes a normal sentence again.
| Write | Say it as | Example | Why it's there |
|---|---|---|---|
| Adding | |||
| INSERT INTO | “please add into…” | INSERT INTO menu (id, item) | Names the table and, in brackets, the columns you will fill. |
| VALUES | “these values…” | VALUES (9, 'Lemon Tart') | The new row, in the same order as the columns. |
| Changing | |||
| UPDATE | “please change…” | UPDATE menu | Names the table to change. |
| SET | “set … to …” | SET price = 3.8 | Says the new value. Change several columns at once with commas: SET price = 3.8, calories = 130. |
| Removing | |||
| DELETE FROM | “please remove from…” | DELETE FROM menu | Removes whole rows, so there is no column list and no star. |
| Choosing rows | |||
| WHERE | “but only where…” | WHERE item = 'Bagel' | The same WHERE as Lesson 1. With UPDATE and DELETE it is your safety belt. |
INSERT INTO menu VALUES (9, 'Lemon Tart', 'food', 3.2, 300). Naming the columns is safer, though, because you can see exactly which value goes where.Playground: change the café's tables
Please type a statement and press Run. INSERT, UPDATE and DELETE really change the tables below, and SELECT reads them. Don't worry about mistakes: Reset tables puts everything back.
Five quick questions
Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.
Your pocket card
Please take a screenshot of this card. Read it once tomorrow and once next week.
INSERTINSERT INTO menu (…) VALUES (…) adds a row.UPDATEUPDATE menu SET … WHERE … changes rows.DELETEDELETE FROM menu WHERE … removes rows.- Add with VALUES. Change with SET. Remove with FROM. And always, always WHERE.
- No WHERE means every row, and there is usually no Undo.
- Look first: run SELECT * with the same WHERE, then swap in UPDATE or DELETE.
- The column list and the VALUES list pair up in order.
- In SET, = means “change it to”. In WHERE, = means “is”.
- Every row needs its own id. Links between tables are protected (foreign keys).
SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.