SQL / INSERTING, UPDATING, AND DELETING ROWS
Updating rows without touching the whole table
Write UPDATE statements that change exactly the rows and columns you intend, using WHERE to scope them and row counts to prove nothing else moved.
What you will learn
- Preview an UPDATE by running a SELECT with the identical WHERE clause first
- Change several columns in one pass; each SET expression reads pre-update row values
- Read the reported row count as blast radius: UPDATE 0 means nothing matched
- Exclude rows that already hold the new value to avoid pointless rewrites
Understanding Updating rows without touching the whole table
An UPDATE statement has two independent halves, and confusing them is what causes accidents. WHERE chooses which rows are touched, and SET chooses which columns inside those rows are rewritten: a column you do not name in SET keeps its value, and a row the WHERE does not match is never written at all. When the WHERE clause is missing the predicate defaults to every row, which is why `UPDATE product SET price = 0` is a perfectly valid statement that quietly rewrites the whole table.
The engine makes one pass over the matched rows, and every expression to the right of an equals sign is evaluated against that row's values as they were before the statement started. That is why `SET price = round(price * 1.10, 2)` is per-row arithmetic rather than one shared number, and why `WHERE stock = 0` cannot re-match a row whose stock you just set to 25 — the row set is decided once, not re-checked as the update proceeds. It also means an update built on a column's own value is not repeatable: run the ten percent increase twice and you get twenty-one percent.
Touching a row costs something even when the new value equals the old one, because triggers fire, an `updated_at` column bumps, and a new row version plus index entries get written. Folding the target value into the predicate, as in `WHERE status IS DISTINCT FROM 'shipped'`, drops those rows out of the statement and out of the count. The count the server reports back is the honest measure of how much of the table you rewrote, so compare it against the count from the SELECT you ran first and treat any mismatch as a reason to stop.
Examples below are PostgreSQL run through psql, which prints a command tag such as `UPDATE 2` after each statement.
CREATE TABLE product (
id integer PRIMARY KEY,
name text NOT NULL,
price numeric(6,2) NOT NULL,
stock integer NOT NULL
);
INSERT INTO product (id, name, price, stock) VALUES
(1, 'mug', 8.50, 40),
(2, 'notebook', 3.00, 0),
(3, 'pen', 1.20, 0),
(4, 'poster', 12.00, 100);
-- Step 1: see exactly which rows the predicate matches.
SELECT id, name, stock FROM product WHERE stock = 0;
-- Step 2: same predicate, now as an update of two columns.
UPDATE product
SET stock = 25,
price = round(price * 1.10, 2)
WHERE stock = 0;
SELECT id, name, stock, price FROM product ORDER BY id;An UPDATE is a single pass over exactly the rows WHERE matched, with every SET expression computed from that row's pre-update values.
Worked examples
One row by key, and a predicate that matches nothing
Shows the row count as the only feedback you get, including the silent case where no row matched.
CREATE TABLE account (id integer PRIMARY KEY, email text, active boolean);
INSERT INTO account VALUES (1, 'a@x.io', true), (2, 'b@x.io', true);
UPDATE account SET active = false WHERE id = 2;
UPDATE account SET active = false WHERE id = 99;
SELECT id, email, active FROM account ORDER BY id;Example explained
Line 1`WHERE id = 2` can match at most one row because id is the primary key, which is the narrowest scope available.
Line 2`UPDATE 1` is the server stating that exactly one row was rewritten; that number is your evidence, not the absence of an error message.
Line 3The second statement reports `UPDATE 0`: id 99 does not exist, so nothing changed and nothing complained.
Line 4Row 1 still shows `t`, because a row outside the predicate is not written and not defaulted.
Different new values for different rows, one statement
Uses CASE inside SET so one pass assigns three fee levels while still leaving non-matching rows alone.
CREATE TABLE invoice (id integer PRIMARY KEY, days_late integer, fee numeric(6,2));
INSERT INTO invoice VALUES (1, 0, 0.00), (2, 12, 0.00), (3, 45, 0.00), (4, 90, 0.00);
UPDATE invoice
SET fee = CASE WHEN days_late >= 60 THEN 25.00
WHEN days_late >= 30 THEN 10.00
ELSE 5.00
END
WHERE days_late > 0;
SELECT id, days_late, fee FROM invoice ORDER BY id;Example explained
Line 1The CASE expression is evaluated once per matched row, so a single write pass produces three different fees.
Line 2`WHERE days_late > 0` keeps invoice 1 out of the statement entirely; without it the ELSE branch would set its fee to 5.00.
Line 3`UPDATE 3` matches the three rows the predicate selected, not the four rows stored in the table.
Scoping the update through another table
Limits the rows written to those whose related row in a second table satisfies a condition.
CREATE TABLE customer (id integer PRIMARY KEY, tier text);
CREATE TABLE orders (id integer PRIMARY KEY, customer_id integer, total numeric(7,2));
INSERT INTO customer VALUES (1, 'vip'), (2, 'basic'), (3, 'vip');
INSERT INTO orders VALUES (10, 1, 100.00), (11, 2, 100.00), (12, 3, 250.00);
UPDATE orders
SET total = total * 0.90
WHERE customer_id IN (SELECT id FROM customer WHERE tier = 'vip');
SELECT id, customer_id, total FROM orders ORDER BY id;Example explained
Line 1The subquery returns ids 1 and 3, and `customer_id IN (...)` turns that result into a row filter on orders.
Line 2`total = total * 0.90` reads each order's own total, so 100.00 and 250.00 shrink by different amounts in the same pass.
Line 3Order 11 keeps 100.00 because its customer is absent from the subquery result, and `UPDATE 2` confirms only two rows were written.
Line 4Arithmetic on numeric is rounded to the column's scale on assignment, so 225.0000 lands in the column as 225.00.
Important notes
Assignment order is dialect-specific: PostgreSQL evaluates all SET expressions from the pre-update row, so `SET a = b, b = a` swaps the columns, while MySQL applies assignments left to right and ends with both columns holding the old b.
Standard SQL and PostgreSQL have no LIMIT on UPDATE; cap a batch with a key range or `WHERE id IN (SELECT id FROM t WHERE ... ORDER BY id LIMIT 1000)`. MySQL's `UPDATE ... LIMIT n` picks arbitrary rows unless you also give an ORDER BY.
Common mistakes
Joining assignments with AND instead of a comma: `SET price = 10 AND stock = 5` is one boolean expression, so PostgreSQL raises a type error and MySQL silently stores 0 or 1 in price and never touches stock.
Re-running a relative update: `SET price = price * 1.10` executed twice raises prices by 21 percent, because the second run reads what the first wrote. Absolute assignments like `SET price = 3.30` are safe to repeat; expressions built on the column are not.
Pulling a value from another table with a subquery that matches nothing: the subquery yields NULL and the column is overwritten with NULL in every matched row, destroying data rather than leaving it alone. Add `AND EXISTS (...)` so those rows fall out of the predicate.
Try it yourself
Change, predict, then run
Recreate the product table from the main example, then write one UPDATE that sets price to 2.00 and stock to 10 for every product priced under 5.00, and check that the server reports UPDATE 2 while mug and poster keep their values. Run the same statement again with `AND price <> 2.00` added and confirm it now reports UPDATE 0.
Open the SQL workspaceCheck your understanding
Three rows hold balances 150, 100, and 90. You run `UPDATE account SET balance = balance - 100 WHERE balance >= 100;` exactly once. What happens, and why?
- Balances become 50, 0, and 90: one pass over the two rows that satisfied the condition before the statement began, each new value computed from that row's own prior balance
- Balances become 50, 0, and -10: SET applies to every row, and WHERE only controls which rows are counted in the reported total
- Balances become 50, 100, and 90: after the 100 row is decremented the engine re-evaluates the condition and rolls that row back out of the update
- The statement fails, because one matched row would end at 0 and no longer satisfies the WHERE condition
Show answer
WHERE is evaluated once per row against the pre-statement image to fix the row set, and SET then computes each new value from that same image, giving 50, 0, and an untouched 90 with a reported count of 2. Option 3 is tempting because it treats WHERE as a condition the row must keep satisfying, but an UPDATE is a single set-based pass, not a loop that revisits rows; and option 4 misreads WHERE as a constraint, when it is only a filter.