UPDATE — Changing Rows
Stationery prices are going up by 10%. One short statement can change every notebook and pen in the catalogue at once. The same statement, missing one line, changes the price of every product the shop sells. UPDATE is powerful precisely because it works on many rows at a time.
The rule
SET says what changes; WHERE says which rows — never forget WHERE.
UPDATE products
SET price = price * 1.10
WHERE category = 'stationery';UPDATE products— which table.SET price = price * 1.10— the new value. The right-hand side can use the row's current values, so each product's own price is raised: a 60.00 notebook becomes 66.00 and a 25.00 pen becomes 27.50.WHERE category = 'stationery'— which rows. Only these change.
To change several columns, separate the assignments with commas:
UPDATE orders
SET status = 'shipped', order_date = '2026-03-06'
WHERE order_id = 106;Without WHERE, every row in the table is updated. There is no warning and no "are you sure?". Some tools add a safety switch (MySQL Workbench's safe-update mode, for example), but you cannot count on one being there.
The shortcut
Select first, then swap. Write the WHERE clause as a SELECT, run it, and look at the rows. When they are exactly the rows you mean to change, replace SELECT * FROM with UPDATE … SET … and keep the WHERE untouched.
Write the SELECT
SELECT * FROM orders WHERE customer_id = 1 AND status = 'pending';
For extra safety on real data, run the UPDATE inside a transaction (BEGIN; … check … COMMIT; or ROLLBACK;), which you will meet properly in a later course.
A classic slip is writing SET status = 'shipped' AND order_date = '2026-03-06'. That is not two assignments; it sets status to the result of a true/false expression — or fails, depending on the database. Commas, not AND.
Drills
Answer fast — trust the rule.
What does UPDATE products SET price = 0; do?
Which statement correctly updates both city and email for customer 2?
A notebook costs 60.00. After UPDATE products SET price = price * 1.10 WHERE name = 'Notebook';, what is its price?
What is the purpose of running SELECT * FROM … WHERE … before an UPDATE with the same WHERE?
Remember
- SET says what changes; WHERE says which rows — never forget WHERE.
- Select first, then swap the head, keeping the WHERE identical.
- Separate assignments with commas, not AND.