Reference

Safe SQL UPDATE & DELETE

Preview rows, check dependencies, and use transactions to reduce the risk of changing more rows than you intended.

UPDATE and DELETE modify stored data. One of the most common and painful mistakes is forgetting the WHERE clause when a change was meant to target only some rows; without it, the statement affects every row in the table.

Practice

Practice UPDATE Practice DELETE Practice transactions Practice UPDATE with CASE

The checklist

Write the WHERE clause as a SELECT first.
Confirm the row count is what you expect.
Eyeball a few of the matched rows.
Run the change inside a transaction when possible.
Re-check the affected row count.
COMMIT only once you're satisfied. Otherwise ROLLBACK.

Preview first

Step 1: SELECT what you'll change

select *
from   orders
where  status = 'cancelled'
  and  order_date < date '2023-01-01'

Then change exactly those rows

Same WHERE, now as DELETE

delete from orders
where  status = 'cancelled'
  and  order_date < date '2023-01-01'

Wrap risky changes in a transaction

Reversible until COMMIT

begin;

update products
set    price = price * 1.10
where  category_id = 2;

-- check the result, then keep or undo:
-- commit;
-- rollback;

Watch out

A bare UPDATE products SET price = 0; with no WHERE resets every product. Previewing the target rows with a matching SELECT before a destructive statement is a simple way to catch many accidental broad updates or deletes.

Dialect note

In the SQLShed Playground, all data lives in a disposable in-browser database. Experiment freely, then reload to reset. On a persistent database, committed changes can be difficult to undo, so verify destructive statements before committing them.

Lesson

What Is SQL DML?

Understand INSERT, UPDATE, and DELETE.

Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Mistake

Why Deduplicating Without ORDER BY Keeps the Wrong Row

ROW_NUMBER() needs an ORDER BY that says which row wins.

Lesson

Adding Rows with SQL INSERT

Add rows and confirm they landed.