PostgreSQL UPDATE ... FROM

Use PostgreSQL UPDATE ... FROM to update rows in one table using values from another table. The FROM table is joined to the target table; a WHERE condition normally identifies which rows match.

Syntax

UPDATE target_table [AS target_alias]
SET column_name = expression [, ...]
FROM source_table [AS source_alias] [, ...]
[WHERE condition]
[RETURNING output_expression [, ...]];

The WHERE clause is optional in PostgreSQL syntax. Include a condition that connects the target and source rows and limits which target rows to update. Without a suitable condition, the statement can update unintended rows.

In the SET list, write the target column without a table or alias prefix: use SET price = ..., not SET p.price = .... You can qualify source columns on the right-hand side, such as u.new_price.

Example: update product prices from another table

Run these statements in the same database session. The primary key on price_updates.product_id ensures that a product can match at most one source row.

CREATE TEMP TABLE products (
    product_id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL
);

CREATE TEMP TABLE price_updates (
    product_id integer PRIMARY KEY,
    new_price numeric NOT NULL
);

INSERT INTO products (product_id, name, price)
VALUES
    (1, 'Notebook', 5.00),
    (2, 'Pen', 2.00),
    (3, 'Mug', 8.00);

INSERT INTO price_updates (product_id, new_price)
VALUES (1, 5.50), (3, 9.25);

Apply the new prices to matching products:

UPDATE products AS p
SET price = u.new_price
FROM price_updates AS u
WHERE p.product_id = u.product_id
RETURNING p.product_id, p.name, p.price;

 product_id |   name   | price
------------+----------+-------
          1 | Notebook |  5.50
          3 | Mug      |  9.25
(2 rows)

The equality condition joins each product to its price update. The pen has no matching source row, so it is not changed. RETURNING shows the updated values; PostgreSQL 18 and later can also return values from before and after the update with old and new—see our guide to RETURNING OLD and NEW row values.

Avoid multiple source matches

Make sure the join produces at most one source row for each target row. If a target row matches multiple rows from FROM, PostgreSQL uses one of them, but which one is chosen is not predictable. Use a unique key, or reduce the source data to one row per target key before updating. The price_updates table above has product_id as its primary key, so it already guarantees at most one matching row.

If your source is a history table with several updates per product, rank those rows first and keep the latest one. This separate table permits multiple updates for one product:

CREATE TEMP TABLE price_update_history (
    update_id integer PRIMARY KEY,
    product_id integer NOT NULL,
    effective_at timestamptz NOT NULL,
    new_price numeric NOT NULL
);

INSERT INTO price_update_history (update_id, product_id, effective_at, new_price)
VALUES
    (1, 1, '2026-06-01 00:00:00+00', 5.25),
    (2, 1, '2026-06-01 00:00:00+00', 5.50),
    (3, 3, '2026-05-01 00:00:00+00', 9.25);

WITH ranked_updates AS (
    SELECT
        product_id,
        new_price,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY effective_at DESC, update_id DESC
        ) AS row_num
    FROM price_update_history
)
UPDATE products AS p
SET price = u.new_price
FROM ranked_updates AS u
WHERE p.product_id = u.product_id
  AND u.row_num = 1
RETURNING p.product_id, p.price;

 product_id | price
------------+-------
          1 |  5.50
          3 |  9.25
(2 rows)

ROW_NUMBER() keeps one source row per product before the join. The unique update_id tie-breaker makes the choice repeatable when two updates have the same effective_at. See the ROW_NUMBER() reference for its numbering behavior.

For the simpler form without a FROM table, see the PostgreSQL UPDATE tutorial. For full syntax and behavior, read the PostgreSQL UPDATE documentation.