Updating rows in PostgreSQL is usually straightforward — until the new values don't live in the same table. Learning how to update a table from another table in PostgreSQL lets you sync denormalized columns, backfill missing values, apply lookup data in bulk, and clean up legacy schemas without writing thousands of individual statements. This leads to that is where UPDATE ... On top of that, fROM becomes one of the most useful, and most misunderstood, tools in the language. This guide walks through the syntax, real examples, the PostgreSQL 15+ MERGE alternative, and the subtle traps that silently corrupt data when ignored.
Some disagree here. Fair enough.
What UPDATE ... FROM Actually Does
PostgreSQL extends the standard UPDATE statement with a FROM clause that behaves much like the FROM clause in a SELECT. The target table is scanned, and for every row that satisfies the WHERE condition, the extra tables listed in FROM are joined in to supply the new values Worth keeping that in mind..
Think of it as a join that writes instead of reads. The engine builds a join between the table you are modifying and the source table (or tables), then applies SET expressions to each matched target row.
One rule matters more than any other: the target table must not appear in the FROM list unless you deliberately want a self-join, and in that case it must be given an alias. Otherwise PostgreSQL raises an error about the table name being specified more than once.
The Basic Syntax, Broken Down
UPDATE target_table AS t
SET column_to_change = source.column_value
FROM source_table AS s
WHERE t.id = s.target_id;
Reading it piece by piece:
UPDATE target_table AS t— the table being modified. The alias is optional but strongly recommended, especially once more than two tables are involved.SET column_to_change = ...— the assignment. The right-hand side can reference any column from theFROMlist.FROM source_table AS s— where the new values come from. This can be a table, a view, a subquery, aVALUESlist, or the result of a function.WHERE t.id = s.target_id— the join condition. Without it, you get a Cartesian product and every target row is updated using an arbitrary source row.
That last point deserves emphasis. Omitting the WHERE clause in an UPDATE ... FROM does not produce an error — it produces wrong data, quietly No workaround needed..
A Practical Example: Syncing Data Between Two Tables
Imagine a schema with customers and orders, where the product team wants a denormalized last_order_total column on customers for reporting speed.
ALTER TABLE customers ADD COLUMN last_order_total numeric(12,2);
UPDATE customers AS c
SET last_order_total = o.total
FROM (
SELECT customer_id,
MAX(total) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
) AS o
WHERE c.id = o.
The subquery in `FROM` is doing important work here: it guarantees exactly **one row per `customer_id`**. Now, this is the single most common source of bugs in `UPDATE ... FROM`, so it is worth internalizing as a habit — always make the source side return one row per target row.
## Joining More Than One Table in the FROM Clause
The `FROM` list is not limited to a single table. You can join several sources together, exactly as in a `SELECT`:
```sql
UPDATE accounts AS a
SET plan_name = p.name,
seat_limit = p.max_seats
FROM subscriptions AS s
JOIN plans AS p ON p.id = s.plan_id
WHERE s.account_id = a.id
AND s.status = 'active';
Here accounts is enriched with data that lives two hops away. Note that the join between subscriptions and plans happens first, and only the resulting rows are matched against accounts.
Using Subqueries Instead of FROM
An alternative style uses a correlated subquery directly in the SET clause: