UPDATE — Changing Rows Safely¶

UPDATE is the U in CRUD. It's also the statement most likely to ruin your day if you forget one clause.

The Anatomy¶

1UPDATE tasks
2SET    status = 'DONE',
3       updated_at = now()
4WHERE  id = 42;

• SET lists the columns to change.
• WHERE chooses which rows. This is not optional in practice.

⚠️ The Most Dangerous Mistake in SQL¶

1UPDATE tasks SET status = 'DONE';   -- 😱 updates EVERY row

Forget the WHERE and you change the whole table. Habits that save you:

1. 1.Write the WHERE clause first, then the SET.
2. 2.Run a SELECT with the same WHERE to preview the affected rows.
3. 3.Wrap risky updates in a transaction so you can ROLLBACK.

Updating with Returning¶

1UPDATE tasks
2SET status = 'DONE', updated_at = now()
3WHERE id = 42
4RETURNING *;

RETURNING * hands back the updated row — handy for sending the fresh state straight to the client.

Conditional & Computed Updates¶

1-- Only advance tasks that are actually in progress
2UPDATE tasks
3SET status = 'DONE'
4WHERE id = 42 AND status = 'IN_PROGRESS';
5
6-- Compute from the current value
7UPDATE users SET login_count = login_count + 1 WHERE id = 7;

The second form is atomic — the database reads and writes in one step, avoiding lost updates under concurrency.

Did Anything Change?¶

UPDATE returns the number of rows affected. If it's 0, the WHERE matched nothing — often the right moment to return a 404 from your API.