UPDATE and DELETE safely

UPDATE changes rows that already exist

UPDATE names the table, SET gives the new values, and WHERE picks the rows. Every row that matches the WHERE is changed; the others are untouched.

UPDATE orders SET status = 'Shipped' WHERE id = 107;

Preview with SELECT first

A safe habit used by careful developers and DBAs: write the WHERE as a SELECT, check the rows and the count, then change SELECT * into UPDATE ... SET or DELETE, keeping the same WHERE. Doing this inside a transaction (topic 4) adds a second safety net.

-- 1. Preview: which rows will change?
SELECT id, name, price FROM products WHERE category = 'Kitchen';
-- 3 | Steel Tiffin Box   | 450
-- 4 | Pressure Cooker 3L | 1450
-- 2. Same WHERE, now the change:
UPDATE products SET price = ROUND(price * 1.10) WHERE category = 'Kitchen';
-- 3 | 495 ,  4 | 1595

Why ROUND? price * 1.10 is a decimal calculation, and 450 * 1.10 is really 495.00000000000006 inside the computer, which SQLite would store as is. Rounding keeps money clean. SET can use the old value of any column of the same row: price = price * 1.10, stock = stock - 1, and several columns at once separated by commas.

UPDATE with a subquery

The new value, or the WHERE, can come from another table through a subquery (module 6). Here every product loses the quantity sitting in Placed orders. The subquery is correlated: it runs for each product, using products.id.

UPDATE products
SET stock = stock - (SELECT SUM(i.qty)
                     FROM order_items i JOIN orders o ON o.id = i.order_id
                     WHERE o.status = 'Placed' AND i.product_id = products.id)
WHERE id IN (SELECT i.product_id
             FROM order_items i JOIN orders o ON o.id = i.order_id
             WHERE o.status = 'Placed');
-- Basmati Rice 40 → 39, Cotton Kurta 30 → 29

DELETE removes rows

DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'Cancelled');
-- 1 row removed (the shoes in order 104)

Delete child rows (order_items) before their parent (orders). With foreign keys switched on, the database refuses to leave items pointing at a missing order, unless the key was declared ON DELETE CASCADE. SQLite only checks foreign keys after PRAGMA foreign_keys = ON.

How many rows changed?

Every UPDATE and DELETE reports a row count, and apps should check it. In SQLite, SELECT changes() returns the rows changed by the last statement; drivers in every language give the same number (rowcount in Python, affectedRows in Node.js MySQL drivers). An unexpected 0 or 5000 means something is wrong.

DELETE, TRUNCATE and DROP

Soft delete

Many apps never really delete customers or orders, because of audits, refunds and legal record-keeping. Instead they set a flag, such as is_active = 0 or deleted_at = '2025-04-20'. This is a soft delete. The cost: every query must remember WHERE is_active = 1, which is why teams often create a view of the active rows (module 10).

Common mistakes

• Running UPDATE or DELETE without WHERE. Write the WHERE first; MySQL Workbench even blocks such statements in its default "safe updates" mode.
• Skipping the preview SELECT. Always check which rows and how many will change.
• Storing price * 1.10 without ROUND and getting 495.00000000000006.
• An UPDATE with a subquery that returns NULL for some rows, wiping their values. Limit the WHERE to rows that have a match, or use COALESCE.
• Deleting parents before children and leaving orphan rows. Delete children first, or use ON DELETE CASCADE on purpose.