UPDATE Join in PostgreSQL

Sometimes you need to update rows in one table using values from another table — for example, applying discount rates stored in a separate table to products, or syncing a denormalized column from an authoritative source. PostgreSQL handles this with the UPDATE ... FROM syntax, which joins the target table to one or more additional tables without using the JOIN keyword.

Syntax

UPDATE target_table
SET target_table.column = source_expression
FROM source_table
WHERE target_table.join_column = source_table.join_column;

The joined table goes in a FROM clause placed immediately after SET, and the join condition is expressed in WHERE. Table aliases keep complex statements readable:

UPDATE t
SET t.col = s.val
FROM source s
WHERE t.id = s.t_id;

Practical Example

Create a products table and a discount_rules table that holds category-level discounts:

CREATE TABLE product_categories (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  discount NUMERIC(4, 2) NOT NULL DEFAULT 0  -- e.g. 0.15 = 15%
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  list_price  NUMERIC(10, 2) NOT NULL,
  sale_price  NUMERIC(10, 2),
  category_id INT REFERENCES product_categories(id)
);

INSERT INTO product_categories (name, discount)
VALUES ('Electronics', 0.10),
       ('Accessories', 0.20),
       ('Furniture',   0.05);

INSERT INTO products (name, list_price, category_id)
VALUES ('Wireless Headset',   129.99, 1),
       ('Laptop Stand',        49.99, 2),
       ('USB-C Hub',           35.00, 2),
       ('Ergonomic Chair',    399.00, 3),
       ('Monitor',            349.99, 1);

Calculate and store the sale_price for every product based on its category discount:

UPDATE products p
SET sale_price = p.list_price - (p.list_price * c.discount)
FROM product_categories c
WHERE p.category_id = c.id;

Output:

UPDATE 5

Verify the results:

SELECT p.name, p.list_price, c.discount, p.sale_price
FROM products p
JOIN product_categories c ON c.id = p.category_id
ORDER BY p.id;

       name        | list_price | discount | sale_price
-------------------+------------+----------+------------
 Wireless Headset  |     129.99 |     0.10 |     116.99
 Laptop Stand      |      49.99 |     0.20 |      39.99
 USB-C Hub         |      35.00 |     0.20 |      28.00
 Ergonomic Chair   |     399.00 |     0.05 |     379.05
 Monitor           |     349.99 |     0.10 |     314.99

Use RETURNING to capture the updated values in the same statement:

UPDATE products p
SET sale_price = p.list_price - (p.list_price * c.discount)
FROM product_categories c
WHERE p.category_id = c.id
  AND c.name = 'Electronics'
RETURNING p.id, p.name, p.sale_price AS new_sale_price;

Multi-Table Update Join

You can join more than two tables by listing additional tables in FROM:

UPDATE products p
SET sale_price = p.list_price - (p.list_price * r.discount)
FROM product_categories c,
     regional_overrides r
WHERE p.category_id = c.id
  AND c.id = r.category_id
  AND r.region = 'EMEA';

Testing with Vela

UPDATE JOIN statements that touch many rows based on business rules from a lookup table are high-risk operations in production. Use Vela database branching to create a branch from your live data, run the update on the branch, and inspect the RETURNING output before applying the same statement to production. Branches are copy-on-write and take no additional disk space until rows are actually modified.

Production Tips

• The FROM clause performs an implicit inner join — rows in the target table with no match in the source table are not updated.
• If the source table can match multiple rows per target row, add a subquery or DISTINCT ON to guarantee a single match and avoid non-deterministic updates.
• Run a SELECT with the same FROM and WHERE conditions before executing the UPDATE to preview exactly which rows and values will change.
• Use RETURNING to capture updated values for audit logging or downstream processing within the same transaction.
• When joining on foreign key columns, an index on the join column in the source table will improve performance significantly on large tables.
• Table aliases are strongly recommended when both tables share column names to avoid ambiguous references in SET or WHERE.