SQL data modification
Review a data fix
The idea in 30 seconds
One-off UPDATE and DELETE statements run against production data are among the riskiest SQL anyone writes. Review them for exactly which rows they touch: check the WHERE clause's logic, preview the rows with a SELECT, run the change in a transaction, and compare the row count with what you expected before committing.
Helps to know first: SQL basics: reading data
On this page
The situation
Support asks for a clean-up: orders that are still pending or paid but were placed before 1 July 2025 are stale and should be cancelled. An AI assistant writes the statement.
Review taskCancel stale orders
Request: “Cancel pending or paid orders placed before 1 July 2025.”
UPDATE orders SET status = 'cancelled'
WHERE status = 'pending' OR status = 'paid' AND ordered_at < '2025-07-01';Would you run it? Write the review you would leave.
Your call
Model review
Request changes. The statement cancels an order it should not.
1. AND binds before OR. PostgreSQL reads the condition as
status = 'pending' OR (status = 'paid' AND ordered_at < '2025-07-01')so it cancels every pending order, of any date, plus paid orders before July. We ran it on the practice data inside a transaction:
| Rows changed | Orders | |
|---|---|---|
| The statement as written | 4 | 102, 106, 107, 111 |
| What was asked for | 3 | 102, 106, 107 |
Order 111 is a pending order from 28 August, well inside the window it should have kept.
2. Fix the logic with IN or brackets. Either says what was meant:
WHERE status IN ('pending', 'paid') AND ordered_at < '2025-07-01'3. Run it so you can check before keeping it:
BEGIN;
UPDATE orders SET status = 'cancelled'
WHERE status IN ('pending', 'paid') AND ordered_at < '2025-07-01'
RETURNING id, status, ordered_at;
-- 3 rows expected: 102, 106, 107. If the count or ids differ: ROLLBACK.
COMMIT;4. Write down the expected count before you run it. “Three orders” in the ticket turns a silent mistake into an obvious one.
Compare it with yours. Did your review catch the main problems?
How to review a one-off data change
- Read the
WHEREaloud with brackets. WhereverANDandORmeet, add brackets that show the intended grouping. If the brackets change the meaning, the original was wrong. - Preview with
SELECT. Run the sameWHEREas aSELECTand look at the rows, not just the count. - Run inside
BEGIN…ROLLBACK/COMMIT, withRETURNING. Compare what changed with what you expected, then decide. - Agree the expected row count in advance. Put it in the ticket or the pull request.
- Think about what else is affected. Rows in other tables that depend on these ones; caches and search indexes holding the old values; anything downstream that reacts to status changes.
Check yourself
How does PostgreSQL read
a OR b AND c?Show answer
As
a OR (b AND c), becauseANDhas higher precedence thanOR.What should you do before running a one-off
UPDATEon production data?Show answer
Agree the expected rows and count, preview them with a
SELECTusing the sameWHERE, then run the update inside a transaction withRETURNINGand commit only if the result matches.Rewrite
status = 'pending' OR status = 'paid' AND ordered_at < '2025-07-01'to mean what support asked for.Show answer
status IN ('pending', 'paid') AND ordered_at < '2025-07-01', or(status = 'pending' OR status = 'paid') AND ordered_at < '2025-07-01'.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 4.1 Lexical StructurePrimary· §4.1.6 Operator Precedence, Table 4.2
AND has higher precedence than OR, and comparison operators bind more tightly than both.
- 2.PostgreSQL documentation: 6.2 Updating DataPrimary· §6.2 Updating Data
UPDATE changes every row its WHERE clause matches.
- 3.PostgreSQL documentation: 6.4 Returning Data from Modified RowsPrimary· §6.4
RETURNING lists the rows a statement changed.
- 4.PostgreSQL documentation: 3.4 TransactionsPrimary· §3.4 Transactions
Changes made inside a transaction can be discarded with ROLLBACK.
- 5.PostgreSQL documentation: 13.3 Explicit LockingPrimary· §13.3.2 Row-Level Locks
UPDATE and DELETE take row-level locks, which are released only at transaction end; a conflicting writer waits for them.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 4.1 Lexical StructurePrimary· §4.1.6 Operator Precedence
One table that settles every "which part runs first" question about an expression.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.