✏️UPDATE
Mutate rows you actually mean to touch
UPDATE assigns new values to columns for every row that satisfies the predicate. Triggers, foreign key cascades, and row-level security can amplify a “small” update into many side writes. Treat a missing WHERE as a production incident in code review, full-table rewrites belong in controlled migrations, not ad hoc shells.
UPDATE inventory SET status = 'ARCHIVED'
Without WHERE, every row matches. Restores from backup are slow; explaining the mistake to an IRB is slower.
Same predicate as a SELECT you just ran
Run SELECT … WHERE … first, capture the row count, then reuse the identical predicate in UPDATE. Use RETURNING or OUTPUT when the engine supports it to verify cardinality.
Single-table update
UPDATE inventory.vials
SET status = 'QUARANTINE',
updated_at = NOW()
WHERE lot_number = 'L-2204'
AND qc_flag = TRUE;| rows_updated |
|---|
| 24 |
Update from a join (PostgreSQL)
UPDATE orders o
SET priority = 'HIGH'
FROM customers c
WHERE o.customer_id = c.customer_id
AND c.segment = 'CLINICAL_TRIAL'
AND o.status = 'OPEN';| rows_updated |
|---|
| 17 |
🗺️Multi-table updates are dialect-shaped
Same idea, derive new values from related rows, different spellings.
| Technique | Typical engines |
|---|---|
| UPDATE … FROM | PostgreSQL |
| UPDATE … JOIN | MySQL, SQL Server |
| MERGE / UPSERT | Insert-or-update in one statement, portability varies |
Hot rows and long transactions
Mass updates on a narrow key range can bloat indexes and heap until autovacuum or maintenance catches up, batch by primary key or timestamp windows, and consider optimistic locking (WHERE version = ?) for concurrent edits to the same entity.
TL;DR
UPDATE changes existing rows matched by WHERE; omitting WHERE updates every row (usually guarded in tools). It respects constraints and may fire triggers; returning clauses can echo changed rows. Wide updates take row/page locks, keep predicates selective and transactions short; use optimistic concurrency columns when two writers can touch the same entity.