Postgres Update Table From Another Table

3 min read

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 the FROM list.
  • FROM source_table AS s — where the new values come from. This can be a table, a view, a subquery, a VALUES list, 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:

Freshly Written

Latest Additions

Related Territory

Readers Also Enjoyed

Thank you for reading about Postgres Update Table From Another Table. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home