Updating and Deleting Data

UPDATE changes existing rows; DELETE removes them. Both are powerful — and dangerous without a WHERE clause.

UPDATE

UPDATE orders
SET status = 'delivered'
WHERE id = 3;

SELECT id, status FROM orders WHERE id = 3;

+----+-----------+
| id |  status   |
+----+-----------+
|  3 | delivered |
+----+-----------+

Set several columns at once, and use expressions:

UPDATE products
SET price = ROUND(price * 0.9, 2), category = 'Sale'
WHERE category = 'Video';

SELECT name, category, price FROM products WHERE category = 'Sale';

+--------+----------+-------+
|  name  | category | price |
+--------+----------+-------+
| Webcam | Sale     | 53.91 |
+--------+----------+-------+

DELETE

DELETE FROM order_items WHERE order_id = 5;
DELETE FROM orders WHERE status = 'cancelled';

SELECT COUNT(*) AS remaining_orders FROM orders;

+------------------+
| remaining_orders |
+------------------+
|                7 |
+------------------+

The order's items were deleted first, because order_items refers to orders. (Whether the database enforces that depends on foreign keys — see Level 2's Constraints lesson.)

Always check your WHERE first

An UPDATE or DELETE without WHERE changes every row:

UPDATE products SET price = 0;   -- every product is now free!

A safe habit:

1. Write the WHERE as a SELECT first, and check it returns exactly the rows you mean.
2. Then turn it into an UPDATE or DELETE.
3. On important data, run it inside a transaction (Level 3), so you can ROLLBACK if the result isn't what you expected.

Soft deletes

Many applications don't really delete important records. Instead they set a column such as deleted_at and filter it out in queries — keeping history and making mistakes recoverable.

Practice

Increase the price of every product in the Audio category by 5%, rounded to two decimals, and show the result.

UPDATE products
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Audio';

SELECT name, price FROM products WHERE category = 'Audio';

+-----------------------------+--------+
|            name             | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 208.95 |
+-----------------------------+--------+