UPDATE and DELETE
Part of the free LogicWiz SQL & Databases course, Module 8: Changing data. The queries and practice run in the app on real Postgres.
An intern wanted to change the price of one Rava Idli to ₹99 and ran UPDATE menu_items SET price = 99;. For the next ten minutes, every dish in the app, Mutton Biryani included, cost ₹99.
You'll learn to
- Change rows with UPDATE … SET … WHERE
- Update from another table with UPDATE … FROM
- Delete rows with DELETE, including with a subquery
- Choose between DELETE, TRUNCATE and DROP
Learn
UPDATE: SET what, WHERE which
UPDATE menu_items
SET price = price + 10
WHERE item_id = 4
RETURNING item_id, name, price;
SET gives the new value, and it can use the old one (price + 10). WHERE picks the rows. RETURNING shows the rows after the change, which is a great way to see what you did.
An UPDATE or DELETE with no WHERE changes every row. A safe habit: first write the SELECT … WHERE … and check the rows, then change SELECT * to UPDATE … SET and keep the same WHERE. On a real database, run it inside BEGIN; and COMMIT; only after the row count looks right.
Several columns, one statement
UPDATE menu_items
SET price = ROUND(price * 1.10, 2),
name = name || ' (Festive)'
WHERE restaurant_id = 2
RETURNING item_id, name, price;
Separate assignments with commas. Every expression on the right uses the old row values, so the order of the assignments does not matter.
UPDATE … FROM: use another table
UPDATE orders AS o
SET delivery_fee = 0
FROM customers AS c
WHERE c.customer_id = o.customer_id
AND c.is_premium
AND o.status = 'pending'
RETURNING o.order_id, c.name, o.delivery_fee;
Premium customers get free delivery on their pending orders. FROM brings in customers, and the WHERE both joins and filters. Only order 1023 (Farhan, premium) changes; order 1020 belongs to Meher, who is not premium.
DELETE with a subquery
DELETE FROM menu_items
WHERE item_id NOT IN (SELECT item_id FROM order_items)
RETURNING item_id, name;
Remove dishes nobody has ever ordered. The subquery lists every ordered item, and NOT IN keeps the rest. It is safe here because order_items.item_id is NOT NULL; the next lesson's trap shows why that matters.
A parent with children cannot just vanish
DELETE FROM customers WHERE customer_id = 1;
Aarav has orders, and orders.customer_id references him. Delete the children first (order items, then orders), or design the key with ON DELETE CASCADE.
DELETE vs TRUNCATE vs DROP
| DELETE | TRUNCATE | DROP TABLE | |
|---|---|---|---|
| Takes WHERE | yes | no | no |
| Removes | the matching rows | every row | rows and the table |
| Speed on lakhs of rows | slow | instant | instant |
| RETURNING / row triggers | yes | no | no |
| Undo inside a transaction (Postgres) | yes | yes | yes |
MySQL writes a join update as UPDATE orders o JOIN customers c ON c.customer_id = o.customer_id SET o.delivery_fee = 0 WHERE c.is_premium; and a join delete as DELETE o FROM orders o JOIN …. MySQL Workbench's safe updates mode refuses an UPDATE or DELETE without a key in the WHERE, for exactly the intern's mistake.
Interviewers ask "how would you safely run an UPDATE on production?" Say: SELECT the rows first with the same WHERE, run it in a transaction, check the row count (or RETURNING), then COMMIT; and keep a backup or audit trail.
In one line
UPDATE t SET col = … WHERE … and DELETE FROM t WHERE …: always WHERE, check with SELECT first, delete children before parents.
Interview corner
What is the difference between DELETE and TRUNCATE?
DELETE is DML: it removes rows one by one, takes a WHERE, fires row triggers and can RETURN the rows. TRUNCATE is DDL: it empties the whole table at once, much faster, with no WHERE, and can reset identity counters. In Postgres both can be rolled back in a transaction.
How do you update a table using values from another table?
In Postgres: UPDATE orders o SET delivery_fee = 0 FROM customers c WHERE c.customer_id = o.customer_id AND c.is_premium;. In MySQL: UPDATE orders o JOIN customers c ON … SET …. A correlated subquery in SET (SET x = (SELECT … WHERE …)) works everywhere.
How would you safely run an UPDATE or DELETE on production data?
Run the same WHERE as a SELECT first and check the rows and count. Then BEGIN;, run the change with RETURNING or check the affected count, and COMMIT; only if it matches (else ROLLBACK). Take a backup or log for big changes, and batch huge deletes so locks stay short.
Why can DELETE … WHERE id NOT IN (subquery) delete nothing?
If the subquery returns even one NULL, x NOT IN (…, NULL) is never true, so no rows match. Filter NULLs out in the subquery, or use NOT EXISTS, which handles NULLs correctly.