UPDATE and DELETE

UPDATE

UPDATE changes values in rows that already exist. SET says what changes; WHERE says which rows:

UPDATE products
SET price = 22.00
WHERE product_id = 2;

SELECT product_id, name, price FROM products WHERE product_id = 2;

Several columns can change at once, and the new values can use the old ones:

UPDATE products
SET price = ROUND(price * 0.9, 2),
    stock = stock + 10
WHERE category = 'Office';

SELECT name, price, stock FROM products WHERE category = 'Office';

Each run takes another 10% off, because it starts from the current price.

DELETE

DELETE removes whole rows:

DELETE FROM order_items WHERE order_id = 105;

SELECT COUNT(*) AS items_left FROM order_items;

The missing WHERE

Leave out the WHERE, and UPDATE or DELETE affects every row in the table. There's no undo button, unless you were inside a transaction.

Transactions: try it, then decide

BEGIN starts a transaction. Changes made after it can be kept with COMMIT, or thrown away with ROLLBACK. Here's the classic mistake, made safely:

BEGIN;
UPDATE products SET price = 0;               -- oops: no WHERE
SELECT name, price FROM products LIMIT 3;
ROLLBACK;
SELECT name, price FROM products LIMIT 3;

The first result shows the damage. After ROLLBACK, the second shows the prices back as they were.

All or nothing

A transaction also keeps related changes together. Recording a sale means adding the item to an order and taking it out of stock, and it should never half-happen:

BEGIN;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (111, 9, 1);
UPDATE products SET stock = stock - 1 WHERE product_id = 9;
COMMIT;

SELECT oi.order_id, p.name, oi.quantity, p.stock AS stock_left
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_id = 111;

If anything fails between BEGIN and COMMIT, the application rolls back, and neither change happens. This is the "A" (atomicity) in the ACID guarantees databases are known for.

Safe habits

1. Run a SELECT with the same WHERE first, and check that it returns exactly the rows you mean to change.
2. Use a transaction for anything important, and look before you COMMIT.
3. Keep backups. Some teams never delete at all, and mark rows as deleted with a column like deleted_at instead.

Foreign keys

Order 101 belongs to customer 1. What should happen if customer 1 is deleted? With foreign key checks switched on, the database refuses, because orders would be left pointing at nobody:

PRAGMA foreign_keys = ON;
DELETE FROM customers WHERE customer_id = 1;

SQLite leaves foreign key checks off unless each connection turns them on, as above; most other databases always enforce them.