UPDATE and DELETE Without Disasters

Changing rows that already exist

UPDATE rewrites columns on existing rows, DELETE removes rows. Both take the same WHERE clause you know from unit 2, and on both, the WHERE clause is the safety catch:

UPDATE pets SET age = 4 WHERE name = 'Biscuit';

DELETE FROM pets WHERE species = 'cat';

SET lists the changes (SET age = 4, species = 'dog' for several). WHERE decides which rows they hit.

Now the classic disaster: forget the WHERE and the statement applies to every row in the table. UPDATE pets SET age = 99; makes every pet 99. DELETE FROM pets; empties the table. No confirmation, no undo (outside a transaction, unit 10). Every engineer knows a story like this.

A targeted UPDATE

Biscuit's age changes from 3 to 4, and nobody else is touched, because the WHERE clause limits the blast radius.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

UPDATE pets SET age = 4 WHERE name = 'Biscuit';

SELECT * FROM pets;

Output

Biscuit|dog|4
Mochi|cat|5
Ziggy|parrot|2

SET names the column and its new value, and WHERE decides which rows receive it. Several columns can be set at once by separating the assignments with commas, as in SET age = 4, species = 'puppy'.

The WHERE clause here works exactly as it does in a SELECT, which is genuinely useful: running the same condition as a SELECT first shows you precisely which rows are about to change.

The missing WHERE, in miniature

This is the classic accident. With no WHERE clause, the update applies to every row in the table, and all three pets become 99 years old.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

UPDATE pets SET age = 99;

SELECT * FROM pets;

Output

Biscuit|dog|99
Mochi|cat|99
Ziggy|parrot|99

No error, no warning, no confirmation prompt. The statement is completely valid SQL and the database carries it out on all three rows, because "no condition" means "every row matches".

On a real table this is thousands or millions of rows overwritten with one value, and the previous values are simply gone. There is no undo for a committed statement, which is why the next section covers the habit that prevents it.

The professional habit

Before running an UPDATE or DELETE on data you care about, run a SELECT with the exact same WHERE clause first:

SELECT * FROM pets WHERE species = 'cat';
-- looks right? then:
DELETE FROM pets WHERE species = 'cat';

The SELECT shows you precisely which rows are about to be affected. If it returns 40,000 rows when you expected 3, you just saved yourself. This costs five seconds and becomes automatic with practice.

Running an UPDATE meant for one row

Intending to give one user a discount but running UPDATE accounts SET balance = balance + 10; grows every account balance by 10.

A missing WHERE means all rows match. The statement is perfectly legal SQL, which is exactly what makes it dangerous: there is nothing for the database to reject and nothing in the output that looks wrong.

It is also unusually hard to undo. Unlike an overwrite to a fixed value, this one is relative, so recovering means subtracting 10 from precisely the rows that were hit, and any legitimate balance change that happened in the meantime muddies the picture.

The habit of writing the SELECT first, and the transactions covered in unit 10, are the two real guard rails here.

A DELETE and an UPDATE in sequence

Two write statements run one after the other, each with its own WHERE clause.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

DELETE FROM pets WHERE name = 'Ziggy';

UPDATE pets SET name = 'Mochi II' WHERE name = 'Mochi';

SELECT * FROM pets;

Output

Biscuit|dog|3
Mochi II|cat|5

DELETE FROM pets WHERE name = 'Ziggy' removes exactly one row, and the UPDATE then renames the cat. Note that DELETE has no column list: it removes whole rows, so there is nothing to name.

Both statements depend on their WHERE clause. DELETE FROM pets; empties the table completely, and the rename without a condition would give all three animals the same name.

The order matters too. Renaming Mochi first would not affect the delete here, but a script that deletes on a value it has already changed will quietly match nothing.