Practise · Change only what you meant

Opening your saved work…

The reading

Change only what you meant

UPDATE menu_items
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Tea';

Seven rows change. Run the preview's SELECT again to see the new prices. Open in the playground

UPDATE menu_items SET price = ROUND(price * 1.05, 2) WHERE category = 'Tea' raises seven prices. SET takes one or more column = value pairs, and the value can be an expression that uses the row's current values. WHERE picks the rows. Leave the WHERE off and the statement is still valid SQL, it still reports success, and it changes every row in the table. There is no confirmation step. The WHERE is the statement.

Read changes() immediately after. It is the number of rows the last statement touched, seven for the tea raise, 45 for the version without a WHERE. A zero means the WHERE matched nothing, which is a result to notice, not to accept.

An UPDATE has to satisfy every constraint an insert does. Set a price to NULL and the engine refuses: NOT NULL constraint failed, and the row is untouched.

Rule of thumb. An UPDATE without a WHERE changes every row and says nothing. Write the WHERE first.

Change only what you meant · UPDATE ... SET ... WHERE, and changes() · level 5 of 6

Every item got the smoothie surcharge

Level 5: Repair it · Exercise 1 of 1

A 25-cent surcharge meant for the four smoothies landed on all 45 items.

This statement was meant to add 0.25 to the price of every Smoothie. It ran, reported success, and changed every row. Fix it so only the Smoothie category changes, and say how you would have caught it before running.

Data: Northshore Coffee. Every run starts from a fresh copy, so nothing you try here is permanent.

I would like help with this exercise

Each run starts from a fresh copy of the data, then shows the rows the question is about.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run to see the rows the question is about.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.