UPDATE Join | PostgreSQL Tutorial

Sometimes you need to update rows in one table using values from another table — for example, recalculating a net price based on a discount stored in a separate segment table. PostgreSQL handles this with the UPDATE ... FROM syntax, which joins the target table to one or more additional tables.

UPDATE join syntax

The joined table goes in a FROM clause placed immediately after SET, and the join condition goes in WHERE:

UPDATE table1
SET table1.c1 = new_value
FROM table2
WHERE table1.c2 = table2.c2;

PostgreSQL matches each row in table1 against every row in table2. When the WHERE condition is true, it applies the SET expression using values from both rows.

Practical example: calculating net price from a segment discount

Given a product table and a product_segment table that holds the discount for each segment, calculate and store the net price for every product:

UPDATE product p
SET net_price = price - price * discount
FROM product_segment s
WHERE p.segment_id = s.id;

This joins each product to its segment, reads the discount column, and writes the computed net_price back to the product row — all in a single pass. Table aliases (p and s) keep the statement concise.

After running the update, verify the results:

SELECT id, name, price, net_price, segment_id
FROM product
ORDER BY segment_id, id;

The net_price column now reflects each product's segment-specific discount.

Practical tips

The FROM clause performs an implicit inner join — rows in the target table that have no match in the joined table are not updated.
If the joined table can match multiple rows per target row, PostgreSQL picks one match non-deterministically. Ensure the join is one-to-one or add additional filters to make it deterministic.
Use table aliases to avoid ambiguous column references when both tables share column names.
Run a SELECT with the same FROM and WHERE conditions first to preview exactly which rows and values will be changed.
You can join more than two tables by adding further comma-separated entries in the FROM clause.

Reference: PostgreSQL documentation — UPDATE.

Frequently Asked Questions

Why does PostgreSQL use FROM instead of JOIN in an UPDATE?

PostgreSQL uses the FROM clause rather than an explicit JOIN keyword for update-joins. The join condition is expressed in the WHERE clause. This is PostgreSQL-specific syntax; other databases like MySQL use a different syntax with an explicit JOIN.

What happens if the joined table matches multiple rows for a single target row?

If the FROM clause produces multiple matching rows for a single row in the target table, only one is used but which one is not deterministic. Ensure your join condition produces a one-to-one relationship, or add additional filters to guarantee a single match.

Can I use UPDATE join with more than two tables?

Yes. Add additional tables to the FROM clause separated by commas, and include the join conditions in the WHERE clause. For example: UPDATE t1 SET col = t2.val FROM t2, t3 WHERE t1.id = t2.id AND t2.category_id = t3.id;

What if a row in the target table has no match in the joined table?

The row is not updated and is left unchanged. The FROM clause behaves like an inner join — unmatched rows are silently skipped.

Can I use RETURNING with an UPDATE join?

Yes. Add RETURNING after the WHERE clause to return columns from the updated rows. You can reference columns from both the target table and the joined table in the RETURNING list.