UPDATE and DELETE — Updating and Deleting Rows

UPDATE — rewriting existing rows

INSERT was for "adding new rows." UPDATE is the operation for rewriting the values of rows that already exist. Changing a member's status, revising a product's price, updating a purchase count — UPDATE is part of everyday operations in any production app.

The basic form is UPDATE table_name SET col = val WHERE condition;. SET says "which column gets which value," and WHERE says "which rows are affected."

-- Rewrite the status of customer where id = 1 to 'active'
UPDATE customer SET status = 'active' WHERE id = 1;

-- Check the result
SELECT * FROM customer;

Forgetting WHERE updates every row

Drop the WHERE from UPDATE customer SET status = 'active' WHERE id = 1; (becoming UPDATE customer SET status = 'active';) and every row of customer has its status rewritten to 'active'. There's no error, which is exactly what makes this an easy way to silently corrupt production data.

Imagine flipping one customer (Alice) from pending to active. (Run it correctly and the explanation will appear.)

① First run SELECT name, status FROM customer WHERE id = 1; and confirm Alice's status is 'pending' (not active yet).

② Update the status column to 'active' for the row in customer where `id = 1` (Alice).

③ Finally, run SELECT * FROM customer; and confirm only Alice's status changed to 'active', while Bob and Carol are unchanged.

(WHERE here uses the same filter syntax you saw in the SELECT article.)

Updating multiple columns at once

By listing `col = val` pairs separated by commas in the SET clause, you can update multiple columns at once: SET col1 = val1, col2 = val2, ....

The right-hand side can also be an expression that includes the column itself. SET price = price + 100 means "rewrite the target row's price to its current price plus 100."

-- Update multiple columns to absolute values at once
UPDATE inventory SET price = 200, stock = 100 WHERE id = 2;

-- Update with expressions (referencing existing values)
UPDATE inventory SET price = price + 100, stock = stock - 10 WHERE id = 2;

Imagine an inventory screen where you want to revise the eraser's price and stock count.

① For the row in inventory where `id = 2` (Eraser), update price to 200 and stock to 100 at the same time.

② Finally, run SELECT * FROM inventory; and confirm only id=2 was updated to the new values, while id=1 (Pencil) and id=3 (Notebook) are unchanged.

Imagine: "raise the pencil's (id=1) price by 100 and reduce its stock by 10." Putting an expression that references the column itself on the right-hand side of SET lets you make changes relative to the current value.

① For the row in inventory where `id = 1` (Pencil), update both columns at once with `price = price + 100` and `stock = stock - 10`.

② Finally, run SELECT * FROM inventory WHERE id = 1; and confirm the pencil's price is 180 and stock is 190.

DELETE — removing rows

DELETE is the operation for removing rows from a table. The basic form is DELETE FROM table_name WHERE condition;, with WHERE narrowing down the target rows. Unlike SELECT, there's no column list (SELECT col1, col2) — DELETE removes whole rows.

The table definition (the column structure) sticks around, so you can INSERT into the same table after deleting. To remove the table itself, use DROP TABLE / TRUNCATE (covered in the next article).

-- Delete all expired subscriptions (status = 'expired')
DELETE FROM subscription WHERE status = 'expired';

-- Check the result (only the surviving rows remain)
SELECT * FROM subscription;

Imagine cleaning up expired subscriptions in bulk.

① From subscription, delete every row where `status` is `'expired'`.

② Finally, run SELECT * FROM subscription; and confirm the only survivors are the 2 rows where status = 'active' (Bob / Dave).

(Every row matching the WHERE condition is targeted — without specifying id, multiple matching rows disappear at once.)

Watch out for missing WHERE — the all-rows update/delete trap

Forgetting WHERE is the most common accident with UPDATE and DELETE. Without WHERE, the syntax is still legal — and the target expands to every row in the table. UPDATE table SET col = val; rewrites every row to the same value; DELETE FROM table; empties the table.

Even if you notice immediately, you can't undo it after the change is committed. The basic operational rule in production is always run the same WHERE as a `SELECT` first to visually confirm the target rows before rewriting.

In production, confirm target rows with SELECT first

For UPDATE / DELETE on a production database, run the same WHERE as a SELECT first and visually confirm the target rows are what you expect before flipping it into the rewrite. The two-step procedure:

1. Run SELECT * FROM table_name WHERE condition; to confirm the target rows (does the row count and the values match what you expected?)

2. If everything looks right, switch the same WHERE into UPDATE table_name SET col = val WHERE condition; or DELETE FROM table_name WHERE condition; and execute

Since the WHERE clause is reused via copy-paste, this also reduces the kind of accident where WHERE gets accidentally dropped during the rewrite.

Imagine: "clear the cache and rebuild it from scratch." The cache_entry table starts the article with 3 cached rows in it.

① Without writing WHERE, run a DELETE that deletes every row of cache_entry.

② Finally, run SELECT * FROM cache_entry; and confirm the result is 0 rows (empty table).

(DELETE without WHERE is a recipe for accidents in production — here it's deliberately the final exercise so you can feel its behavior firsthand.)