The Missing WHERE

Reading is forgiving, writing is not

Every exercise so far has only read data. A wrong SELECT gives a wrong answer and nothing else happens; run it again with a better WHERE and no harm was done.

UPDATE and DELETE are different. They change what is stored, and WHERE is the only thing standing between "change this row" and "change all of them".

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, jars INTEGER);
INSERT INTO hives (label, jars) VALUES ('north hedge', 14), ('orchard', 31), ('lane end', 6);

UPDATE hives SET jars = 0;

SELECT id, label, jars FROM hives ORDER BY id;

id | label       | jars
---+-------------+-----
 1 | north hedge |    0
 2 | orchard     |    0
 3 | lane end    |    0
(3 rows)

One hive was robbed and all three are now empty. There is no error, no warning and no confirmation: an UPDATE with no WHERE is a legal statement whose meaning is "every row". The same is true of DELETE FROM hives, which empties the table. SQLite does not even have a TRUNCATE TABLE, because that is what a DELETE with no condition already does.

The three-step habit

This is worth doing every single time, and it takes seconds:

1. SELECT with the WHERE you are about to use. If it shows more rows than you expected, stop.
2. Run the write, with that same WHERE.
3. SELECT again to confirm the result.

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, jars INTEGER);
INSERT INTO hives (label, jars) VALUES ('north hedge', 14), ('orchard', 31), ('lane end', 6);

SELECT id, label, jars FROM hives WHERE label = 'lane end';

UPDATE hives SET jars = 9 WHERE label = 'lane end';

SELECT id, label, jars FROM hives ORDER BY id;
SELECT changes() AS rows_changed;

id | label    | jars
---+----------+-----
 3 | lane end |    6
(1 row)

id | label       | jars
---+-------------+-----
 1 | north hedge |   14
 2 | orchard     |   31
 3 | lane end    |    9
(3 rows)

rows_changed
------------
           1
(1 row)

changes() is SQLite's way of saying how many rows the last statement touched, and it is a free sanity check: a number you did not expect means a WHERE clause you did not mean. PostgreSQL and MySQL report the same thing through their client libraries instead.

Transactions make a mistake undoable

BEGIN opens a transaction, COMMIT makes everything since permanent, and ROLLBACK throws it all away. Inside a transaction you can look before you leap:

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, jars INTEGER);
INSERT INTO hives (label, jars) VALUES ('north hedge', 14), ('orchard', 31), ('lane end', 6);

BEGIN;
UPDATE hives SET jars = 0;
SELECT label, jars AS inside_the_transaction FROM hives ORDER BY id;
ROLLBACK;

SELECT label, jars AS after_rollback FROM hives ORDER BY id;

label       | inside_the_transaction
------------+-----------------------
north hedge |                      0
orchard     |                      0
lane end    |                      0
(3 rows)

label       | after_rollback
------------+---------------
north hedge |             14
orchard     |             31
lane end    |              6
(3 rows)

The UPDATE really happened — the query inside the transaction proves it — and ROLLBACK put the table back exactly as it was. Swap that word for COMMIT and the zeroes stay. A transaction is also how you make two changes that must both happen or neither: mark a loan returned and mark the tool available, with no chance of stopping halfway.

Constraints are a different kind of safety net

The table in the editor declares name TEXT NOT NULL and deposit INTEGER NOT NULL CHECK (deposit >= 0). Those refuse values that cannot be right:

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT NOT NULL, jars INTEGER CHECK (jars >= 0));
INSERT INTO hives (label, jars) VALUES ('north hedge', 14);
SELECT label, jars FROM hives;
UPDATE hives SET jars = -3 WHERE label = 'north hedge';

label       | jars
------------+-----
north hedge |   14
(1 row)

Error in the statement on line 4: CHECK constraint failed: jars >= 0

Useful, and no help at all against the mistake in this exercise: 1250 is a legal deposit, so a constraint has no opinion about it landing on five rows instead of one. Constraints police values; WHERE polices rows.

One SQLite-specific warning while you are here. A FOREIGN KEY is the one constraint SQLite does not enforce by default — you have to put PRAGMA foreign_keys = ON; at the top of the script, per connection. PostgreSQL and MySQL enforce them always, so a schema that looks protected in SQLite may not be.

Your turn

The editor holds a five-row tools table and a three-step script. The job is a single correction: the cordless drill's deposit goes up to 1250, and nothing else changes.

Press Run first and read the third table. Every deposit is 1250, because the UPDATE has no WHERE.

Make two changes:

1. Add a WHERE clause to the UPDATE so it only touches the drill.
2. Add the same WHERE clause to the step-1 SELECT, so it shows you the one row you are about to change.

Run it again. The first table should show one row, and the last should show the drill at 1250 with the other four untouched.

Then change COMMIT to ROLLBACK and run it again, to watch the correction disappear.

If something goes wrong

If every deposit is still 1250, the WHERE clause went onto the wrong statement — it needs to be on the UPDATE, before the semicolon.

If the drill's deposit did change but you used double quotes round its name, the query only worked because SQLite quietly treats an unmatched double-quoted name as text. PostgreSQL would have failed with column "cordless drill" does not exist. Use single quotes for values.

If you get CHECK constraint failed: deposit >= 0, the new deposit is negative. The table refuses that, by design.

Nothing here can break anything real. The database is built fresh on every run and thrown away the moment it finishes, which is exactly why this is a good place to make this mistake once.