UPDATE (and the Missing-WHERE Disaster)

The projector on screen 2 needs a repair, so tonight's Paper Lanterns is moving to screen 1. In the screenings table that showing is one row — screening 88 — and one value in it is now wrong. For the first time in this book, Lora is not adding data or reading it. She is editing a fact that already exists. The verb is UPDATE.

This page teaches the verb, and then it stages the most famous mistake in all of SQL — slowly, on paper, at zero cost, so that it happens to you here instead of at work. The mistake is small. It is the WHERE clause, missing.

The Anatomy of an UPDATE

An UPDATE has two working parts. SET says what changes — which column, and its new value. WHERE says which rows the change applies to. The WHERE is the same clause you have used since Chapter 3, with the same operators and the same behavior; only the consequence is new. In a SELECT, WHERE chose which rows you saw. Here, it chooses which rows are altered.

UPDATE screenings
SET screen = 1
WHERE screening_id = 88;

The database answers with a count: 1 row updated. That count is not decoration. Professionals read it every single time, because it is the first — and often the only — signal that the statement touched exactly what you meant. Expected one, got one: the edit landed, screening 88 now says screen 1, and the old value 2 is simply gone. UPDATE edits in place; it does not keep the previous version anywhere.

The Disaster, in Slow Motion

Now the version this page exists for. Same evening, same intention, same statement. Lora types the first two lines, and between them and the semicolon, the lobby phone rings. She answers it, comes back, sees a statement that looks complete, and sends it.

UPDATE screenings
SET screen = 1;

The database answers with a count: 212 rows updated.

Every screening the Marquee has ever scheduled is now on screen 1. Tonight's shows. Next week's. The past screenings from March, which were history and were correct until a moment ago. Both screens' entire calendars, folded into one. There was no error message, because from the database's point of view nothing went wrong: an UPDATE with no WHERE means all rows, and it did exactly that, promptly and thoroughly.

Sit with the two counts for a moment. 1 row updated and 212 rows updated came from statements that differ by one missing line. The old screen values are not stored anywhere; there is no undo command waiting. If you have ever pressed Replace All in a document when you meant to fix one selected word, you already know this accident from the inside — find-and-replace did what you said, across everything you pointed it at. WHERE is the selection. That is the whole mapping, and the last time this page needs it.

The SELECT-First Reflex

The professional defense is not general caution. It is one specific habit: before running an UPDATE, run a SELECT with the exact WHERE you are about to use, and read what comes back.

SELECT screening_id, screen, starts_at
FROM screenings
WHERE screening_id = 88;

One row comes back: screening 88, currently on screen 2, tonight at 18:30. Those are the rows the UPDATE will touch — exactly those, no others. The reason the rehearsal is trustworthy is the identity you noticed earlier: WHERE in UPDATE is WHERE from Chapter 3, unchanged. The SELECT shows precisely what the UPDATE will match, and the number of rows it returns is the count the UPDATE will report. If the SELECT surprises you — two hundred rows instead of one, or zero — you have just been saved, for the price of one extra query.

Had Lora rehearsed her broken statement, the SELECT with no WHERE would have poured all 212 screenings onto her screen. Hard to miss, sitting there, before anything was at stake.

No Confirmation Dialog

Apps have trained you to expect a last chance: "Are you sure?", a red button, a countdown. The database offers none of that. It treats "change all 212 rows" as an ordinary request — because sometimes it is one; end-of-season price changes touch every row on purpose. The tool cannot tell your intentions from your accidents, so it executes what you wrote, immediately. The confirmation step is a habit you bring, not a feature it has.

One honest preview: Chapter 7 adds a real seatbelt — a transaction lets you make a change, inspect the result, and take it back before it becomes permanent. Until then, the SELECT-first reflex is your entire safety system, and it is a good one.

SET Does Arithmetic Too

One more capability rounds out the verb. The new value in SET does not have to be a constant — it can be computed from the row's current values. Lora wants ten percent off every remaining screening of The Long Rain:

UPDATE screenings
SET price = price * 0.9
WHERE film_id = 1;

Read price = price * 0.9 as an instruction, not an equation: for each matched row, take that row's current price, multiply it by 0.9, and store the result back into price. A 9.50 screening becomes 8.55; an 11.00 screening becomes 9.90 — each row computes from its own value. This is the everyday shape of real-world updates: raises, discounts, counters ticking up. And notice the WHERE is still there, doing what it always does. The rows it matches are the blast radius; nothing else on this page changes that.