8.2 UPDATE & DELETE — Safely — The Complete SQL Course

UPDATE changes existing rows. DELETE removes existing rows. Both are powerful enough to fix data in one statement — and powerful enough to damage every row in a table if the target is vague.

The safety habit from here on is simple and non-negotiable: write the target as a SELECT first, inspect the rows, then change only the verb. You are not proving that SQL syntax works. You are proving that the row set is exactly the one you intend to change.

UPDATE: rehearse the target, then change it

The café has a small task list. Ken finished the grinder calibration task, so first rehearse the row:

SELECT task_id, task_name, owner, status
FROM shift_tasks
WHERE task_id = 2;
 
UPDATE shift_tasks
SET status = 'done'
WHERE task_id = 2;
 
SELECT task_id, task_name, owner, status
FROM shift_tasks
WHERE task_id = 2;

 task_id |     task_name     | owner | status
---------+-------------------+-------+--------
       2 | Calibrate grinder | Ken   | open
(1 row)
UPDATE 1
 task_id |     task_name     | owner | status
---------+-------------------+-------+--------
       2 | Calibrate grinder | Ken   | done
(1 row)

The first grid is the rehearsal: one row, the right task, the old value visible. The command tag says UPDATE 1, so one row changed. The final grid verifies the new value. That three-part loop is the unit of safe data changes: rehearse, write, verify.

The missing-WHERE catastrophe

The classic destructive mistake is not a syntax error. It is a valid statement whose target is every row because there is no WHERE.

Here the rehearsal is intentionally bad: it shows the whole table. The write is wrapped in a transaction and rolled back so you can see the damage without keeping it:

BEGIN;
 
SELECT task_id, task_name, status
FROM shift_tasks
ORDER BY task_id;
 
UPDATE shift_tasks
SET status = 'done';
 
SELECT status, count(*) AS tasks
FROM shift_tasks
GROUP BY status
ORDER BY status;
 
ROLLBACK;
 
SELECT status, count(*) AS tasks
FROM shift_tasks
GROUP BY status
ORDER BY status;

BEGIN
 task_id |       task_name        |  status
---------+------------------------+----------
       1 | Restock beans          | open
       2 | Calibrate grinder      | done
       3 | Clean patio            | open
       4 | Print menus            | done
       5 | Call linen service     | open
       6 | Archive April specials | canceled
(6 rows)
UPDATE 6
 status | tasks
--------+-------
 done   |     6
(1 row)
ROLLBACK
  status  | tasks
----------+-------
 canceled |     1
 done     |     2
 open     |     3
(3 rows)

Joined updates: rehearse the join

Sometimes the new value comes from another table. The extra risk is join fan-out or the wrong join key, so the rehearsal should show both the old value and the incoming value.

SELECT st.task_id,
       st.owner,
       st.priority AS old_priority,
       pc.new_priority
FROM shift_tasks AS st
JOIN priority_changes AS pc
  ON pc.owner = st.owner
WHERE st.status = 'open'
ORDER BY st.task_id;
 
UPDATE shift_tasks AS st
SET priority = pc.new_priority
FROM priority_changes AS pc
WHERE pc.owner = st.owner
  AND st.status = 'open';
 
SELECT task_id, owner, status, priority
FROM shift_tasks
WHERE owner IN ('Ada', 'Tomas')
ORDER BY task_id;

 task_id | owner | old_priority | new_priority
---------+-------+--------------+--------------
       1 | Ada   | high         | urgent
       3 | Tomas | low          | normal
(2 rows)
UPDATE 2
 task_id | owner | status | priority
---------+-------+--------+----------
       1 | Ada   | open   | urgent
       3 | Tomas | open   | normal
(2 rows)

The rehearsal proves two things: which task rows will be touched, and what value each row will receive. The write uses PostgreSQL's UPDATE ... FROM form. If that grid has duplicates or surprising owners, the update is not ready.

DELETE: rehearse what will disappear

DELETE removes rows, so the rehearsal grid is the last easy look at the data. Canceled tasks are safe to remove only after the target is visible:

SELECT task_id, task_name, status
FROM shift_tasks
WHERE status = 'canceled'
ORDER BY task_id;
 
DELETE FROM shift_tasks
WHERE status = 'canceled';
 
SELECT count(*) AS remaining_tasks
FROM shift_tasks;

 task_id |       task_name        |  status
---------+------------------------+----------
       6 | Archive April specials | canceled
(1 row)
DELETE 1
 remaining_tasks
-----------------
               5
(1 row)

The command tag is your first verification: DELETE 1 means one row was removed. The count is the second verification, and it is computed by the database, not guessed.

Joined deletes: rehearse the join, then remove

When another table identifies the rows to remove, the same rule applies: first run the join as a SELECT.

SELECT st.task_id,
       st.task_name,
       tp.reason
FROM shift_tasks AS st
JOIN tasks_to_purge AS tp
  ON tp.task_id = st.task_id
ORDER BY st.task_id;
 
DELETE FROM shift_tasks AS st
USING tasks_to_purge AS tp
WHERE tp.task_id = st.task_id;
 
SELECT task_id, task_name, status
FROM shift_tasks
ORDER BY task_id;

 task_id |  task_name  |    reason
---------+-------------+---------------
       4 | Print menus | printed twice
(1 row)
DELETE 1
 task_id |     task_name      | status
---------+--------------------+--------
       1 | Restock beans      | open
       2 | Calibrate grinder  | done
       3 | Clean patio        | open
       5 | Call linen service | open
(4 rows)

The write uses PostgreSQL's DELETE ... USING form. Joined writes deserve extra suspicion because a bad join can widen the target. If the rehearsal grid is wrong, the write will be wrong in exactly the same way.

TRUNCATE, DELETE, and DROP

Use the smallest destructive tool that matches the intention.

DELETE removes chosen rows and supports a WHERE target. TRUNCATE empties a whole table; it is not for row-by-row predicates. DROP removes the table object itself. In everyday application work, most careful cleanup starts with a rehearsed DELETE, because you can inspect exactly which rows are about to disappear.

Try the whole ritual in the playground. Change the rehearsal first, then make the write match it.

sql — playgroundlive

⌘/Ctrl + Enter to run

You now have the write-side survival rule: rehearse the exact row set, run the change, verify the command tag and the data. Next lesson handles the harder "insert or update?" problem with upserts and MERGE.

Checkpoint

Answer all three to mark this lesson complete

UPDATE & DELETE — Safely