UPDATE modifies rows that already exist. It's the most dangerous statement you've seen so far, and the reason is arithmetic: if you get an INSERT wrong you create one extra row and you delete it; if you get an UPDATE wrong you overwrite correct data with incorrect data and the previous value stops existing. There's no recycle bin. There's no "undo". There's only last night's backup, if there is one.

That's why this lesson devotes its first half to something that isn't syntax: the professional protocol for running an UPDATE without wrecking anything. Write the SELECT first, count the rows, wrap it in a transaction, check, and only then commit. It's a thirty-second habit separating someone who has spent years touching databases from someone who has spent a month. After that will come everything UPDATE can do: several columns at once, calculations over the previous value, updates driven by another table with FROM, RETURNING to see exactly what you've changed, and the property —idempotence— that determines whether a script can be rerun without fear.

⚠️ Safety warning

UPDATE is a destructive operation: it replaces existing data without keeping the previous value.

• Run all of this lesson's examples on your practice database (greenstore), never on a production system.
• Take a backup beforehand ahead of any test: pg_dump -U sql_course -d greenstore -f backup.sql. Getting back to the initial state is as easy as running greenstore.sql again.
• On a real system, an UPDATE over live data should be reviewed by another person and run inside a transaction.

1. The syntax, and the WHERE as a safety net
2. What exactly happens if you forget the WHERE
3. The five-step protocol
4. BEGIN … ROLLBACK / COMMIT: the real net
5. Updating several columns at once
6. Computing from the previous value: raising prices by 5 %
7. Why the order of the assignments doesn't matter
8. UPDATE ... FROM: updating from another table
9. RETURNING: seeing what you've changed
10. An UPDATE that violates a constraint
11. Updating to NULL, and the effect of ON UPDATE CASCADE
12. Idempotence: why it matters when a script is rerun
13. Four GreenStore business cases
14. Common Mistakes and Tips
15. Exercises
16. Conclusion

Three pieces: which table, which columns change and to what value, and which rows. Only the third is optional for the engine; none of them is optional for you.

A minimal example: product 13 (Soy wax candles) has been sitting at stock 0 for months and the decision is to discontinue it.

UPDATE N tells you how many rows it modified. That number is your first line of defence: if you were expecting one row and it says UPDATE 17, something has gone very wrong — but at least you know.

One nuance about that counter: PostgreSQL counts the rows that matched the WHERE, not the ones whose value changed. If you update a column to the value it already had, the row counts all the same:

One row "modified" even though the stock was already 0. It's important to know: UPDATE 1 means "one row matched the condition", not "one row changed".

And the critical point, already flagged at the end of module 4: in the logical execution order, an UPDATE's WHERE does exactly the same job as a SELECT's. It determines the set of affected rows.

That's the reason module 5 is the natural continuation of module 4: the WHERE you've spent four modules sharpening is the same one. What changes is the consequence of getting it wrong.

There's no mystery: the UPDATE applies to every row of the table.

All twenty products in the catalogue have just gone up by 5 %. Not just the Drinks: all of them. And the previous price no longer exists anywhere.

(The first 5 of 20 rows.)

And here's the truly insidious part: PostgreSQL hasn't complained. No error, no warning, no confirmation. The statement is syntactically perfect and semantically legal. The engine did exactly what you asked. The only clue is that UPDATE 20 instead of UPDATE 4, and to see it you have to be looking.

Now picture the same statement on a products table of 40,000 references in a real shop, at seven o'clock on a Friday evening. It isn't a made-up story: it's one of the industry's most repeated incidents.

Why SQL doesn't protect you. Some graphical clients warn you if they detect an UPDATE or a DELETE with no WHERE, and psql even has an option for it (\set ON_ERROR_STOP on doesn't do this, but tools like pgcli or DBeaver do). But the SQL standard doesn't provide for it: an UPDATE with no WHERE is a legitimate operation —sometimes it's exactly what you want, for instance when filling in a new column—. The protection has to come from your process, not from the language.

This is the habit. Five steps, thirty seconds, zero incidents.

The task: "raise the price of every Drinks product by 5 %".

Before writing the word UPDATE, write the query that locates exactly the rows you want to touch.

Four. It matches what we expected (Drinks has 4 products, as you checked in 04-06). If the number surprises you, stop. That surprise is the most valuable signal you're going to get.

Write the SET expression as a calculated column of the SELECT and look at them:

Every price is reasonable. No odd rounding, no negatives, no surprises.

Copy the WHERE literally. Don't rewrite it from memory: copy it.

Four, the same number as in step 2. If it had said UPDATE 20, you'd know instantly that the WHERE had got lost along the way.

Identical to step 3's preview.

A detail about types worth understanding. We didn't write ROUND(...) in the UPDATE and even so the prices came out with two decimals. The reason is that price is NUMERIC(10,2): when assigning 3.25 * 1.05 = 3.4125 to that column, PostgreSQL rounds on storing (3.41). It works, but it's implicit rounding. If the result matters to you —and with money it always matters—, write the explicit ROUND so whoever reads your code knows the decision was yours and not the type system's.

The previous section's protocol reduces the risk enormously, but it doesn't remove it. The complete safety net is the transaction.

In PostgreSQL, each standalone statement runs and commits on its own (autocommit). With BEGIN you open a block where nothing is definitive until you say COMMIT, and where ROLLBACK undoes it all:

Now, before committing, verify:

If it's right:

And if it's wrong —or if that UPDATE 4 had been an UPDATE 20—:

As if it had never happened. That's the power you've gone four modules without using.

The complete flow, which from here on you should apply whenever you touch data:

Three practical warnings about transactions, just enough to use them today:

This is only the tip. The complete model of transactions —the ACID properties, the isolation levels, the locks and the deadlocks— is the whole of module 9. Here you use it as a working tool: open, check, commit or undo. With that alone you're far safer than you were ten minutes ago.

And an alternative when a transaction isn't enough, because the change is large or irreversible: make a copy of the table first.

It's the legitimate use of CTAS 05-01 announced. Remember it doesn't copy constraints (05-01, section 8): it's good for restoring values, not for replacing the table.

They're separated by commas inside the same SET:

A single UPDATE with two assignments is always better than two UPDATEs, and not just because you type less:

That "visible intermediate state" is the weighty argument: between the first UPDATE and the second there's an instant in which the product is inactive but with 55 units of stock. Another session can read it right there.

On the right-hand side of the = you can use any expression, the row's own columns included:

The rule, and it's the key to the whole of the next section: the right-hand side is evaluated with the values the row had BEFORE the UPDATE.

An example with CASE… well, not with CASE yet (module 6). An example with arithmetic and a column from another row of the same table won't do either: that's a subquery (module 7). With what you have today, the useful expressions are 02-02's: arithmetic, concatenation and basic functions.

The relative margin comes out identical in both, and it's no coincidence: by fixing the price as cost * 2.2, the relative margin is always 1 − 1/2.2 = 0.5455, whatever the cost. It's the check that the formula does what it was meant to.

Notice the AND cost IS NOT NULL. In GreenStore every product has a cost, but the column is nullable (05-01): without that filter, a product with no cost would have received price = NULL, and since price is NOT NULL the whole statement would have failed. When the SET does arithmetic with a nullable column, the WHERE has to protect you — it's 04-03's three-valued logic applied to writing.

This is the deepest difference between UPDATE and an imperative language, and it surprises everybody arriving from Java, Python or C.

In Python, price = cost * 2 followed by cost = cost * 1.1 would use the original cost on the first line and modify it on the second. In SQL the same thing happens, but for a different and stronger reason: it isn't that the assignments run in order, it's that they're all evaluated simultaneously over the previous row.

Product 5 had price = 1.95 and cost = 0.90:

price = 0.90 × 2 = 1.80 (with the old cost, not with 0.99). cost = 0.90 × 1.1 = 0.99.

And now the definitive proof, which in an imperative language would need a temporary variable:

They were at 9.75 and 5.40; now they're at 5.40 and 9.75. They've been swapped. In Python you'd have needed tmp = price or a multiple assignment; in SQL it's the default behaviour.

Three practical consequences:

1. You can write the assignments in any order. SET a = ..., b = ... and SET b = ..., a = ... are equivalent.
2. You can't chain calculations within the same UPDATE. SET price = price * 1.1, vat = price * 0.21 would compute the VAT on the old price, not on the newly raised one. If you need to chain, you need two UPDATEs (inside the same transaction).
3. You can't assign the same column twice. PostgreSQL rejects it:

So far, the new values came from the row itself. Often they come from another table. PostgreSQL solves this with a FROM clause in the UPDATE, which works just like a SELECT's FROM: it brings in additional tables and relates them to the one you're updating through the WHERE.

We come back to order 21 which we recorded in 05-02 (customer 6, three lines: 2 olive oils, 1 honey, 3 chamomile teas). The stock still has to be deducted:

120 − 2, 80 − 1, 180 − 3. Exactly right.

⚠️ The deadly trap of UPDATE ... FROM. If a row of products matches several rows of order_lines, PostgreSQL doesn't add them up: it picks one of the matches, non-deterministically, and applies that one. No error, no warning. If order 21 had two lines of the same product, this UPDATE would deduct only one of them.

It's exactly module 3's row multiplication, but far more dangerous: in a SELECT you see it because extra rows come out; here it's invisible. The correct solution involves aggregating first (SUM(ol.quantity) grouped by product) and updating against that result, which requires a subquery in the FROM — module 7. In the meantime: before an UPDATE ... FROM, always check the matching is 1:1.

That check is done like this:

Zero rows: there are no repeated products and the matching is 1:1. Now we're safe.

We've just raised Drinks by 5 % (section 3). The orders already shipped or delivered must keep their historical price —that's the entire reason unit_price exists (01-05)—, but the ones that haven't gone out yet should bill the new tariff.

A single line. The rest of the lines of orders 18, 19 and 20 already match the catalogue.

Notice two things. First: in an UPDATE's FROM you can put several tables, separated by commas or joined with JOIN. Second: the table being updated (order_lines) isn't repeated in the FROM. If you put it there, PostgreSQL would treat it as a second, independent instance and the result would be a silent Cartesian product.

This is one of the module's biggest divergences:

And the SQL standard, in fact, defines none of the four: the portable form is a correlated subquery in the SET, which you'll see in module 7.

Just as in INSERT, RETURNING returns the affected rows. In UPDATE it's even more useful, because it shows you the result:

Two orders that had been "shipped" for months move to "delivered". And you see it without needing a later SELECT.

What RETURNING can't do: return the previous value. It only sees the already-updated row. If you need to keep the old value, you have three options: copy the table beforehand (section 4), an INSERT ... SELECT into an audit table before the UPDATE (05-02), or an audit trigger (module 10).

Dialect note: SQL Server can do it: its OUTPUT DELETED.column, INSERTED.column clause returns the before and the after in the same statement. It's one of the few things where its syntax beats PostgreSQL's.

An UPDATE is subject to exactly the same constraints as an INSERT, and it gives the same errors. The difference is that now the row already existed and was correct.

CHECK:

Product 13 had stock 0 and CHECK (stock >= 0) stops it going down to −1. That CHECK, which in 05-01 looked like bureaucracy, has just prevented a negative stock.

A domain CHECK:

UNIQUE:

FOREIGN KEY:

NOT NULL:

In all five cases, the original row is left untouched: an UPDATE that fails modifies nothing. And as with INSERT, if the UPDATE affected ten rows and one of them violates a constraint, none of them is updated.

You do it with = NULL, not with IS NULL (which is a comparison operator, 04-03):

Order 2 stops having a sales rep assigned: it starts behaving like the ten web orders. There would now be 11 orders with employee_id IS NULL.

You can only do it if the column allows nulls. And a semantic warning carried over from 04-03: NULL means "unknown", not "none". Setting employee_id to NULL to mean "the web handled it" is correct because the schema defines that null that way. Setting salary = NULL to mean "they're paid 0" would be a design error: 0 and "I don't know" are different things.

When a parent's primary key value changes, ON UPDATE decides what happens to the children. GreenStore doesn't use it because its PKs are surrogate and never change (01-05). With an example table you can see it clearly:

Now the company decides to migrate to two-letter ISO codes:

The two child rows have updated themselves. Without ON UPDATE CASCADE, the UPDATE would have failed:

And here's 01-05's argument in its sharpest form: with surrogate keys this problem doesn't exist. ON UPDATE CASCADE is the answer to a question only somebody who chose a mutable natural key ever asks.

An operation is idempotent if running it twice gives the same result as running it once. It's a crucial property in any automated process, because sooner or later a script runs twice: it fails halfway and is relaunched, it gets deployed twice, somebody isn't sure whether it ever ran.

Compare:

With the first, starting from the original price of €3.25:

The price runs away without anybody noticing. With the second, the result is €3.41 all three times.

The rule that follows:

And the three ways of living with a non-idempotent UPDATE when the relative calculation is what you need:

1. Make it idempotent through the WHERE. If you can identify the "not yet processed" rows, the second pass finds nothing:

The first run returns UPDATE 2; the second, UPDATE 0, because there's no longer any order in shipped status with that date. A well-chosen WHERE turns a relative operation into an idempotent one. It's the most important pattern in this section.

2. Record that it's already been done, in a control table or with a flag column, and check it beforehand.

3. Wrap it in a versioned migration, which by definition is applied only once. That's 05-06's topic.

Golden tip: when you write a script that's going to run unsupervised, always ask yourself "what happens if this runs twice?". If the answer is "a silent disaster", rewrite it.

All of them on the freshly reloaded database, with the complete protocol.

Already solved in section 3, with its preliminary SELECT, its count of 4 rows, its preview and its verification. It's the canonical case.

From 14 delivered to 16, and the shipped status disappears. It matches the 2 rows announced. Note the use of the half-open date range (< DATE '2026-02-01') following the course's convention.

Customer 8 lets us know his address is wrong:

A single-row correction, the most frequent operation of all. Two observations:

• We filter by id, not by email. Filtering by the value you're about to change works, but if there were a typo in the WHERE you'd touch no rows at all (UPDATE 0) and you might believe it was already fine. The PK is always the safest filter.
• The UNIQUE is still watching. If the new address already belonged to another customer, the UPDATE would fail with duplicate key value violates unique constraint "customers_email_key".

Product 13 (Soy wax candles) has never been sold and has been at zero for months:

This is a soft delete, and it's the correct alternative to DELETE FROM products WHERE id = 13. The product disappears from the sales catalogue but keeps its row, its history and its referential integrity. The active column exists for exactly this, and the topic gets a full treatment in the next lesson.

What to remember today: from now on, every catalogue query needs WHERE active:

Eighteen on sale (number 13 has just joined number 20, which was already inactive). That FILTER is 04-04's, now useful for auditing the result of a write.

• Forgetting the WHERE. It modifies every row with no warning. It's the module's emblematic mistake and the reason for the five-step protocol.
• Rewriting the WHERE from memory when moving from the SELECT to the UPDATE. Copy it literally. Half of all incidents come from a condition that "was almost the same".
• Not looking at the UPDATE N. It's the only warning you're going to get. Always compare it with the prior count.
• Working without BEGIN. In autocommit there's no way back. A BEGIN costs five letters.
• Leaving a transaction open. It locks rows for every other session. Commit or roll back before you get up.
• Expecting RETURNING to give the previous value. It only sees the new one. For the before, copy the table or audit it.
• Chaining calculations in a single SET. SET a = a * 1.1, b = a * 0.21 uses the old a to compute b. If you need to chain, two UPDATEs.
• Using UPDATE ... FROM with a 1:N matching. It doesn't add up: it picks an arbitrary match and gives no warning. Check with a GROUP BY ... HAVING COUNT(*) > 1 beforehand.
• Repeating the target table in PostgreSQL's FROM. It produces a silent Cartesian product. In SQL Server it's the other way round: you have to repeat it.
• Arithmetic over a nullable column without protecting it in the WHERE. price = cost * 2 with a null cost gives NULL and blows up against the NOT NULL.
• Writing non-idempotent UPDATEs in automated scripts. price = price * 1.05 run twice raises by 10.25 %. Set absolute values or narrow it down with the WHERE.
• Updating a column that represents history. unit_price is the price at the moment of the sale. Touching it on already delivered orders falsifies the accounts.
• Tip: filter by the primary key whenever you can. It's the safest WHERE there is.
• Tip: preview the SET as a calculated column. Seeing the new values before writing them catches rounding, negatives and unexpected nulls.
• Tip: for big changes, CREATE TABLE copy AS SELECT * FROM table first. Thirty seconds that are worth a whole night.

Work on the freshly reloaded database and use BEGIN … ROLLBACK so you don't drag changes from one exercise into the next.

Management approves a price review for Natural cosmetics (category 2) with these rules:

1. Raise the price by 8 %, rounding to two decimals.
2. Raise the cost by 3 %, rounding to two decimals.
3. It must only affect the active products.

Apply the complete protocol: preliminary query, count, preview with the relative margin before and after, UPDATE inside a transaction, verification and COMMIT. Then answer: has the relative margin improved or worsened? Why?

A colleague hands you this UPDATE with the comment "I want to bring the line prices of every order that hasn't shipped yet up to date, and I'm getting a strange number":

1. Find the error and explain what this statement really does.
2. Rewrite it correctly, with aliases, and say how many rows it should touch on the freshly reloaded database.
3. Explain why this operation would be catastrophic if the WHERE included orders.status = 'delivered'.

The warehouse has taken a delivery and stock needs replenishing. The data comes in this auxiliary table:

1. Write the UPDATE ... FROM that adds the units received to each product's stock.
2. Check beforehand that the matching is 1:1.
3. Show the state before and after, and explain what happens to product 13.
4. Is this UPDATE idempotent? If it isn't, what would happen if the warehouse ran the script twice by mistake, and how would you avoid it?

Solution 1

4 rows. All four Natural cosmetics products are active.

The relative margin improves on all four products (from an average of 0.5448 to 0.5660), and the reason is arithmetic: the price goes up 8 % and the cost only 3 %, so the difference grows faster than the price. It's the check that the price review does what management intended.

Two decisions in the brief you had to respect: the explicit ROUND (even though NUMERIC(10,2) would round the same way, writing it makes the intent visible) and the AND active filter, which discards no row here but shields the statement against a future discontinued product.

Solution 2

1. The error. order_lines appears twice: once as the UPDATE's target table and once inside the FROM. PostgreSQL treats them as two independent instances, so the one in the FROM isn't correlated with the one being updated. The result is a silent Cartesian product: the condition order_lines.product_id = products.id is resolved against the FROM's instance, and the UPDATE ends up touching every line in the table, giving them an arbitrary price. Hence "the strange number".

PostgreSQL even warns about something similar in the documentation, but it gives no error: the statement is legal.

2. The correct version:

Zero rows on the freshly reloaded database. The only pending order is number 20, and its two lines (products 2 and 18, at €3.90 and €3.50) already match the catalogue. That UPDATE 0 is valuable information, not a failure: it confirms nothing is out of date.

Three changes with respect to the original: aliases have been added, order_lines has been removed from the FROM, and AND ol.unit_price <> p.price has been added so as not to touch rows that are already fine (which, incidentally, makes the statement idempotent).

3. Why it would be catastrophic with 'delivered'. unit_price holds the price at the moment of the sale: it's 01-05's deliberate denormalisation and the basis of all the historical accounting. Rewriting it with the current price would mean that invoices issued a year ago change their amounts retroactively.

The concrete damage to GreenStore: lines 1 and 4 carry historical prices (€11.95 and €17.50) against the current ones (€12.50 and €18.90). This UPDATE would rewrite them and the total revenue would go from €727.95 to a different figure, invalidating every quantity published in modules 3 and 4. And there'd be no way of getting the old values back: they'd no longer be anywhere.

It's the perfect example of an UPDATE that runs with no error, with no warning, and destroys unrecoverable information.

Solution 3

No repeated product: each row of products will match a single row of warehouse_receipt.

What happens to product 13. It goes from 0 to 25 units: it stops being out of stock. It's a change with consequences beyond the table, because product 13 is one of the course data set's "deliberate gaps" (01-06): it had stock 0 and had never been sold. If you leave this change committed, some examples from earlier modules will stop matching. Reload the script before continuing.

4. Idempotence. It isn't idempotent. SET stock = stock + units is a relative operation: running it twice would add the units twice, and the warehouse would have 200 units of olive oil in the system and 160 on the shelf. An inventory discrepancy nobody would spot until the annual stocktake.

The three ways to avoid it, from least to most robust:

1. Flag the receipts already processed. Add a processed BOOLEAN NOT NULL DEFAULT FALSE column to warehouse_receipt, filter by WHERE NOT r.processed in the UPDATE and set it to TRUE in the same transaction. The second run finds nothing.
2. Record every movement in a stock_movements table with its own unique key, and compute the stock as the sum of the movements instead of storing it as a mutable value. It's the ledger approach, the most robust one and the one serious inventory systems use.
3. Turn it into a versioned migration, which by construction is applied only once (05-06).

And the property that makes option 1 work: whenever you can identify the "not yet processed" rows in the WHERE, a relative operation becomes idempotent. It's the same pattern as case 13.2, where WHERE status = 'shipped' guarantees the second pass returns UPDATE 0.

UPDATE is the course's first statement that can destroy information, and you now know how to handle it:

• The syntax UPDATE table SET col = value WHERE ..., where the WHERE does the same job as in a SELECT but with different consequences. UPDATE N counts the rows that matched the condition, not the ones whose value changed.
• With no WHERE, every row is modified: 20 products instead of 4, with no error, no warning and no way back.
• The five-step protocol: SELECT with the final WHERE → count rows → preview the new values as a calculated column → turn it into an UPDATE copying the WHERE literally → verify. And comparing the prior count with the UPDATE N as an alarm.
• BEGIN … verify … COMMIT / ROLLBACK as a real safety net, with its three practical warnings: it locks rows, an error aborts the whole block, and sequences aren't undone. The complete model —ACID, isolation, locks— is module 9.
• Several columns in a single SET, which avoids the visible intermediate state of two consecutive UPDATEs.
• Calculations over the previous value (SET price = price * 1.05) and the golden rule: all the assignments are evaluated simultaneously over the old row. That's why the order doesn't matter, why SET a = b, b = a swaps two columns with no temporary variable, and why calculations can't be chained.
• UPDATE ... FROM for updating from another table, with its deadly trap: a 1:N matching doesn't add up, it picks an arbitrary match and gives no warning. And the four incompatible syntaxes of PostgreSQL, MySQL, SQL Server and Oracle.
• RETURNING to see the result, which can't return the previous value (except through SQL Server's OUTPUT).
• The five constraint errors, identical to INSERT's, with the original row untouched when they fail.
• Updating to NULL and its semantics, and ON UPDATE CASCADE, which is only needed with mutable natural keys.
• Idempotence: SET col = fixed_value yes, SET col = col * k no; and the pattern that solves it, narrowing the WHERE to the "not yet processed" rows so the second run returns UPDATE 0.
• And the real cases: a category price rise, closing old orders, correcting an email and discontinuing with active = FALSE, the soft delete.

That last case is the door to the next lesson. In The DELETE Statement you'll learn to remove rows for real —with the same protocol, reinforced—, you'll see the difference between DELETE and TRUNCATE, you'll check with row counts what happens when you delete a referenced row depending on whether its ON DELETE is RESTRICT, CASCADE or SET NULL, and you'll arrive at the underlying question: if active = FALSE keeps the history and DELETE destroys it, why ever delete anything? The answer has to do with auditing, with performance and with the right to erasure of personal data.