UPDATE

UPDATE changes existing rows — say exactly what you mean

UPDATE sets new column values for the rows matched by WHERE. The arithmetic in SET must match your stated intent — a 50% raise is × 1.5, not × 2 (which is a 100% raise / doubling).

-- Increase salary by 50% for department 2:
UPDATE Employees
SET    salary = salary * 1.5      -- +50%  (salary * 2 would be +100%)
WHERE  department_id = 2;

intent	expression
increase by 50%	salary * 1.5 or salary + salary * 0.5
increase by a flat 5000	salary + 5000
double (increase by 100%)	salary * 2

The one habit that prevents disasters: an UPDATE without a WHERE updates every row. Run the same filter as a SELECT first to see which rows you'll hit, and wrap multi-statement changes in a transaction so you can ROLLBACK. In interactive clients, prefer BEGIN; UPDATE …; SELECT …; -- inspect; COMMIT or ROLLBACK.

Multi-table / join UPDATE forms

-- MySQL: join in UPDATE
UPDATE employees e
JOIN departments d ON d.id = e.department_id
SET e.salary = e.salary * 1.1
WHERE d.name = 'Engineering';

-- PostgreSQL: UPDATE … FROM
UPDATE employees e
SET salary = e.salary * 1.1
FROM departments d
WHERE d.id = e.department_id AND d.name = 'Engineering';

-- Portable: correlated subquery
UPDATE employees e
SET salary = salary * 1.1
WHERE department_id IN (
  SELECT id FROM departments WHERE name = 'Engineering'
);

Join UPDATEs must keep the join key so each target row matches at most one source row (or the engine may error or apply non-deterministic values). Prefer deterministic keys.

Concurrent UPDATE hazards (TOCTOU)

The classic app bug: read in the application, decide, write back — without a transaction or atomic expression.

-- ❌ Race: two requests both read balance=100, both write 100-30=70 → one debit lost
// app: bal = SELECT balance FROM accounts WHERE id=1;
// app: UPDATE accounts SET balance = bal - 30 WHERE id=1;

-- ✅ Atomic expression in one statement (lost-update closed for this pattern)
UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 30;

-- ✅ Multi-step: lock the row
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- app logic using the locked balance
UPDATE accounts SET balance = … WHERE id = 1;
COMMIT;

TOCTOU (time-of-check to time-of-use): the gap between SELECT and UPDATE is where another session commits. Isolation level alone at READ COMMITTED does not make read-modify-write safe — you need FOR UPDATE, a single atomic UPDATE, or SERIALIZABLE with retry. (Deep dive II covers races in production detail; this is the fundamentals-level rule.)

Idempotence and row counts

• Check ROW_COUNT() / cmd.RowsAffected when the business requires "exactly one row updated."
• Optimistic concurrency: UPDATE … SET …, version = version + 1 WHERE id = ? AND version = ? — 0 rows means someone else won; retry.

Takeaways

• +50% = × 1.5; × 2 is +100%. Match the math to the words.
• No WHERE = update all rows. Preview with SELECT, guard with a transaction.
• Join/FROM forms exist per engine; keep joins 1:1 to the target row.
• Never app-level read-then-write without FOR UPDATE or an atomic UPDATE expression — that is a lost-update race.

---

Re-authored for correctness and concurrency depth for this guide (the prior version's prose said +50% while the code doubled the salary; multi-table UPDATE and TOCTOU races added). See also: WHERE Clause, Transactions & ACID, Isolation Levels.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — UPDATE

Why this exists / the decision it encodes

UPDATE exists to change matched rows exactly as specified. The decisions that matter: correct arithmetic for the business words (+50% = ×1.5), mandatory WHERE discipline, join form that stays 1:1 to the target row, and concurrent read-modify-write safety (atomic expression or FOR UPDATE).

Worked example with numbers or traced SQL/FD

-- +50% for dept 2: salary = salary * 1.5  (not * 2)
-- WHERE-less: updates ALL rows — always SELECT the filter first
-- Race (TOCTOU):
T1: read bal=100; T2: read bal=100; T1 write 70; T2 write 70 → one debit lost
Fix atomic: UPDATE accounts SET balance = balance - 30 WHERE id=1 AND balance >= 30;
Fix lock: BEGIN; SELECT … FOR UPDATE; … UPDATE; COMMIT;
Optimistic: UPDATE … SET version=version+1 WHERE id=? AND version=? — 0 rows = conflict

When NOT / named alternative

Do not app-read then write without a transaction lock or single-statement atomic update. Do not multi-table UPDATE with joins that fan out one target to many sources (non-deterministic). Batch large updates in chunks to avoid long locks and replication lag.

Failure mode / ops fingerprint / interview trap

Fingerprint: lost updates on wallet/inventory; mass-update of all rows from missing WHERE in prod. Interview: isolation READ COMMITTED alone does not fix classic lost update — need locking, atomic UPDATE, or SERIALIZABLE+retry.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K12: UPDATE is the home of lost-update races; state isolation + locking precisely. K13: multi-table UPDATE join cardinality is a correctness constraint.

Hostile-panel drills (with model answers)

Q1. Write the expression for a 50% raise vs double.
Model answer: 50% raise: salary * 1.5 (or salary + salary*0.5). Double / +100%: salary * 2.

Q2. Why does SELECT then UPDATE lose a debit under RC?
Model answer: Both transactions can read the same balance before either commits the write; last writer wins and one decrement disappears. Atomic UPDATE or FOR UPDATE serializes the critical section.

Q3. How do you verify exactly one row was updated?
Model answer: Check ROW_COUNT()/RowsAffected; for optimistic concurrency require version match and treat 0 as conflict to retry.

I just read a lesson on **UPDATE** (Databases) and want to truly understand it. Explain UPDATE from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **UPDATE** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **UPDATE** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **UPDATE** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.