Change only what you meant
UPDATE ... SET ... WHERE, and changes()
Prices, phone numbers and wages that need to change for some rows and not others.
Can you
- Change one or more columns of exactly the rows the WHERE names
- Use the row's current value in the new value
- Catch a missing WHERE before it touches every row
The reading
Read the whole chapterChange only what you meant
UPDATE menu_items
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Tea';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.
The ladder
Start at the first rung even if the idea looks familiar: it is answerable from the reading alone, and the climb is where the difficulty lives. A wrong answer sends you back to the paragraph that teaches it, then to a hint, then to the worked answer.
Learn
- Practise this rung
Level 1: Watch it answered
- Practise this rung
Level 2: Fill the blank
Practise
- Practise this rung
Level 3: Predict the result
- Practise this rung
Level 4: Write it yourself
Master
- Practise this rung
Level 5: Repair it
- Practise this rung
Level 6: Transfer it
Keep practising
The whole bank for this concept, in any order, with the same review, hint and worked answer.
Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.