SQL Café · Lesson 6 of 8

Please change the menu board.

So far: SELECTFROM … JOIN … ONWHEREGROUP BYHAVINGORDER BYLIMITCOUNT SUM AVG MIN MAX Lesson 1 · Lesson 2 · Lesson 3 · Lesson 4 · Lesson 5

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.

You say to the café manager “Please add a new item to the menu: a Lemon Tart, number 9, food, £3.20, 300 calories. Thank you!”
↓ same thing in SQL
INSERT INTO menu (id, item, type, price, calories) VALUES (9, 'Lemon Tart', 'food', 3.2, 300);
The one thing to remember

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 Add a new row INSERT INTO menu (…) VALUES (…)

Pin up a new card. The new row appears in green in the playground.

UPDATE Change rows UPDATE menu SET … WHERE …

Rub out and rewrite. Changed cells appear in amber.

DELETE Remove rows DELETE FROM menu WHERE …

Take the card down. Removed rows are crossed out in red.

Green, amber, red, like traffic lights. Adding is safe, changing needs care, removing needs the most care.
INSERT

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.

Columnsiditemtypepricecalories
↓↓↓↓↓
Values9'Lemon Tart''food'3.2300
Same rules as before: text goes in 'quotes', numbers don't. Prices are written without the £ sign: £3.20 is just 3.2.
Every row needs its own id. The database refuses two rows with the same id, because the id is how other tables (like orders) point at a row.
UPDATE

SET means “change it to”

UPDATE names the table, SET says which column gets which new value, and WHERE says which rows to change.

Before
itemprice
Espresso£2.50
Cappuccino£3.50
Iced Latte£4.50
UPDATE menu SET price = 3.8 WHERE item = 'Cappuccino'
itemprice
Espresso£2.50
Cappuccino£3.50 £3.80
Iced Latte£4.50
One symbol, two jobs. In SET, = means “change it to”. In WHERE, = means “is”, a test. Same symbol, different job, depending on where it is.
You can use the old value. SET price = price + 0.2 means “make the price 20p more”. The right side uses the price as it was before the change.
The golden safety rule

No WHERE means every row

UPDATE and DELETE without WHERE change or remove every single row in the table.

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.

Step 1: look firstSELECT * FROM menu WHERE item = 'Fruit Cup';Check that only the rows you mean come back.
Step 2: then changeDELETE 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.

The database looks after you

It protects the links between tables

Orders point at menu items by their number. So the database stops you from breaking those links:

You can't delete a menu item that has orders. Cappuccino (id 2) has two orders. If it disappeared, those orders would point at nothing. So DELETE FROM menu WHERE id = 2 is refused.
You can't add an order for an item that doesn't exist. There is no menu item 12, so an order with item_id 12 is refused.
This protection is called a foreign key. Our café's database has one on orders.item_id, pointing at menu.id.
Three patterns to learn by heart

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…”
Add with VALUES. Change with SET. Remove with FROM. And always, always WHERE.
Read the code one word at a time

Every piece has a meaning, even the two = signs

Please press Next (or click any piece) and watch the sentence grow underneath.

Say it out loud
Today's words and symbols

Say each one out loud

Swap each word for what you say, and the code becomes a normal sentence again.

WriteSay it asExampleWhy 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 menuNames the table to change.
SET“set … to …”SET price = 3.8Says the new value. Change several columns at once with commas: SET price = 3.8, calories = 130.
Removing
DELETE FROM“please remove from…”DELETE FROM menuRemoves 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.
Can I leave out the column list? Yes, if you give a value for every column in the table's own order: 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.
Your turn

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.

Mission

Ctrl + Enter also runs
Or try one of these
    The tables right now
    Lock it in

    Five quick questions

    Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

    Take this with you

    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.