Stored data changes: users update email addresses, product prices move, and orders go from pending to paid. UPDATE does not add rows; it modifies one or more columns in existing rows.
UPDATE users names the table, SET email = ... supplies the new value, and WHERE id = 1 selects the target row. Only Alice changes.
UPDATE users
SET email = '[email protected]'
WHERE id = 1;WHERE is critical in UPDATE
UPDATE users SET city = 'Tokyo'; overwrites city in every row. One missing WHERE can expand the scope from one user to thousands. Know exactly what the condition matches.
SELECT before UPDATE
Put the intended WHERE into SELECT first. When the returned rows are exactly the target, run UPDATE. The extra seconds prevent many mistakes.
Update several columns or rows
Separate several SET assignments with commas, such as changing Bob's email and city together. WHERE can also match several rows; if two users live in Tokyo, WHERE city = 'Tokyo' changes both. A tool's “2 rows affected” message is important evidence.
Calculate from the current value
A new value can use the old one: SET price = price + 10, or price = price * 1.10 for Electronics. Without WHERE, every product changes.
Writing NULL
SET city = NULL stores a missing value; SET city = 'NULL' stores ordinary text. Query the target again after the update.
UPDATE users
SET email = '[email protected]',
city = 'Tokyo'
WHERE id = 2;Modify target rows and see the scope
Updates persist in the sandbox. Compare a precise WHERE, a wider condition, and no WHERE by watching affected rows.
| id | name | city | |
|---|---|---|---|
| 1 | Alice | [email protected] | Tokyo |
| 2 | Bob | [email protected] | Osaka |
| 3 | Chris | [email protected] | Tokyo |
| 4 | Diana | [email protected] | Kyoto |
Sandbox ready; no real database is connected.KNOWLEDGE QUIZ
Update safely with WHERE
Complete two tasks: (1) change Mouse at id 2 to 45.00; (2) raise Electronics prices by 10% without affecting other categories. For each, write a preview SELECT, the UPDATE, and a verification SELECT. State the expected affected-row count.
Optional: portable practice data
DROP TABLE IF EXISTS products;
CREATE TABLE products (id INTEGER, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50));
INSERT INTO products VALUES
(1,'Keyboard',80.00,'Electronics'), (2,'Mouse',40.00,'Electronics'),
(3,'Coffee Mug',15.00,'Home'), (4,'Backpack',60.00,'Fashion');