The UPDATE syntax is:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case I think you want this:

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

Or if you need to join on two or more tables:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;
Answer from Mark Byers on Stack Overflow
Top answer
1 of 16
1315

The UPDATE syntax is:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case I think you want this:

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

Or if you need to join on two or more tables:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;
2 of 16
316

The answer of Mark Byers is the optimal in this situation. Though in more complex situations you can take the select query that returns rowids and calculated values and attach it to the update query like this:

with t as (
  -- Any generic query which returns rowid and corresponding calculated values
  select t1.id as rowid, f(t2, t2) as calculatedvalue
  from table1 as t1
  join table2 as t2 on t2.referenceid = t1.id
)
update table1
set value = t.calculatedvalue
from t
where id = t.rowid

This approach lets you develop and test your select query and in two steps convert it to the update query.

So in your case the result query will be:

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

Note that column aliases are mandatory otherwise PostgreSQL will complain about the ambiguity of the column names.

🌐
Neon
neon.com › postgresql › tutorial › update-join
PostgreSQL UPDATE Join with Practical Examples
... The output indicates that the net_price column has been updated with the correct values. Use the PostgreSQL UPDATE join statement to update data in a table based on values in another table.
Discussions

PostgreSQL update join vs SQL Server update join - Database Administrators Stack Exchange
UPDATE foo SET bar = t2.bar FROM foo t1 JOIN foo2 t2 ON t1.id = t2.id; But running in Postgres, the query is glacially slow. More on dba.stackexchange.com
🌐 dba.stackexchange.com
Update with inner join
You've already introduced movimento_venda in the UPDATE clause, unless you want a self-join you don't then introduce it again in the FROM clause. What you do have to do when using a FROM clause is say, in the WHERE clause, how the FROM clause relates to the table being updated. Since you are only introducing one other table, venda, place it in the from clause then put the ON condition you've written into the where clause. update m_v set ... from venda where venda... = m_v... ... More on reddit.com
🌐 r/PostgreSQL
2
0
February 6, 2023
help with query to update two columns on two tables
Generally update updates one table, so I'd write this as: update usr set full_name = 'Test New' where id = 123; update usr_email set email = 'test@test.com' where usr_id = 123; Best wrapped in the same transaction, so either all or none of the updates succeed. More on reddit.com
🌐 r/PostgreSQL
9
9
October 23, 2020
Occasional very slow simple UPDATE on a single row
I can't think of anything that would make a simple update like that take a full hour(?!) except for a idle transaction holding a lock. If you set log_lock_waits = on and check for log lines like: process xxxxx still waiting for ShareLock on transaction xxxxxx after xxxxxxx ms then that should at least tell you for sure if it's a locking issue or not. More on reddit.com
🌐 r/PostgreSQL
7
3
October 22, 2014
🌐
PostgreSQL
postgresql.org › docs › current › sql-update.html
PostgreSQL: Documentation: 18: UPDATE
May 14, 2026 - A similar result could be accomplished with a join: UPDATE accounts SET contact_first_name = first_name, contact_last_name = last_name FROM employees WHERE employees.id = accounts.sales_person; However, the second query may give unexpected results if employees.id is not a unique key, whereas the first query is guaranteed to raise an error if there are multiple id matches.
🌐
TigerData
tigerdata.com › learn › using-postgresql-update-with-join
Using PostgreSQL UPDATE With JOIN | Tiger Data
December 10, 2025 - While standard UPDATE statements modify records in a single table, UPDATE with JOIN allows you to use data from related tables to determine which rows to update and what values to set.
🌐
GeeksforGeeks
geeksforgeeks.org › postgresql › how-update-join-works-in-postgresql
How UPDATE JOIN Works in PostgreSQL? - GeeksforGeeks
July 23, 2025 - Where those IDs match, it updates ... in one table using values from another related table in PostgreSQL by using the FROM clause in an UPDATE statement....
🌐
CommandPrompt Inc.
commandprompt.com › education › how-to-use-update-join-in-postgresql
How to Use UPDATE JOIN in PostgreSQL — CommandPrompt Inc.
June 1, 2023 - UPDATE JOIN is used to alter the values of the first table using the reference column from the other tables. This guide has demonstrated the use of UPDATE JOIN in PostgreSQL with examples.
🌐
RisingWave
risingwave.com › home › blog › mastering postgresql update with join for efficient data management
Mastering PostgreSQL Update with Join for Efficient Data Management | RisingWave
July 2, 2024 - By assigning meaningful aliases to tables involved in UPDATE operations with JOINs, users can streamline query interpretation and facilitate efficient communication of data relationships. By adhering to these best practices and steering clear of common pitfalls associated with postgres update with join in PostgreSQL, database administrators can elevate their proficiency in managing data updates across interconnected tables effectively.
Find elsewhere
🌐
MySQLCode
mysqlcode.com › home › postgresql update join: introduction, syntax & example
PostgreSQL UPDATE Join: Introduction, Syntax & Example - MySQLCode
June 28, 2023 - Basically, you can say, the UPDATE join is the combination of the UPDATE and the SELECT query. Now let’s see the syntax of the UPDATE join statement and examples as well. Following is the syntax of the UPDATE join statement.
Top answer
1 of 2
19

But running in Postgres, the query is glacially slow.

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.)

Could be fixed by adding a join condition like:

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

Well, technically, a WHERE condition, but all the same.

But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead:

UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

The manual advises for the FROM clause of UPDATE:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

And:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example:

  • Nullify column in update if subquery returns empty
2 of 2
4

I want to address part of the question from a SQL Server perspective.

In T-SQL I would do an update using a join using something like this:

UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

Maybe something like that, but probably not exactly that. It is too easy to end up with an accidental cross join. The SQL Server documentation doesn't really talk about that, but it should because this trips people up all the time.

Algebrization of UPDATE...FROM can be quirky (often for backward-compatibility), so it is important to be explicit and follow best practice. This means:

  1. Assigning an alias
  2. Updating that alias; and
  3. Ensuring the update is deterministic i.e. each target row is updated from at most one source row.

Extracts from the same documentation page:

If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

and, revealing some of the algebrization issues:

When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results.


So the T-SQL example would be:

CREATE TABLE dbo.foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE dbo.foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

-- Update the alias
UPDATE F1
SET F1.bar = F2.bar
FROM dbo.foo AS F1
JOIN dbo.foo2 AS F2
    ON F2.id = F1.id -- deterministic;

One way to check an update is deterministic in SQL Server is to first write it as a MERGE, which does check for non-deterministic updates at runtime. You would not routinely write a simple update as a merge for performance reasons (and maybe because merge has a few issues of its own).

🌐
KoalaTea site
koalatea.io › postgres-update-join
Working with Update Join in Postgres - KoalaTea
December 14, 2023 - UPDATE employees JOIN salaries AS s ON employees.emp_no = s.emp_no SET first_name = 'Alex', last_name = 'Tam', s.salary = 8000 WHERE s.salary < 70000;
🌐
Techpresso
dupple.com › home › blog › postgresql update join (actually update from) 2026 guide
PostgreSQL UPDATE JOIN (Actually UPDATE FROM) 2026 Guide | Dupple Blog
May 4, 2026 - PostgreSQL syntax is `UPDATE target SET col = source.col FROM source WHERE target.id = source.id`. The MySQL and SQL Server UPDATE JOIN syntax does not exist in PostgreSQL. When the source table has multiple rows that match the target row, ...
🌐
Databasefaqs
databasefaqs.com › home › postgresql update join
PostgreSQL Update Join - DatabaseFAQs.com
July 1, 2025 - The most common approach to perform an update join in PostgreSQL is using the UPDATE statement with a FROM clause. This method is straightforward and readable. UPDATE table1 SET column1 = table2.column1 FROM table2 WHERE table1.id = table2.id;
🌐
EDUCBA
educba.com › home › data science › data science tutorials › postgresql tutorial › postgresql update join
PostgreSQL UPDATE JOIN | How UPDATE JOIN works in PostgreSQL?
May 12, 2023 - In some cases, we need to update ... table in the statement, we have to define the PostgreSQL FROM clause with the joined table and specify the PostgreSQL WHERE clause with a JOIN condition....
Address   Unit no. 202, Jay Antariksh Bldg, Makwana Road, Marol, Andheri (East),, 400059, Mumbai
🌐
Reddit
reddit.com › r/postgresql › update with inner join
r/PostgreSQL on Reddit: Update with inner join
February 6, 2023 -

Guys, Im trying to update using another table data as reference to set a column of the first table when the 'where' conditions being completed, but it's still updating the whole table, Im just lost right now, can anyone help ? This is my code:

update movimento_venda

set situacao = 2

from movimento_venda mv

inner join venda v on(v.id_movimento_venda = mv.id_movimento_venda)

where v.numero_cupom = 946431

and v.total_venda = 10.25

and cast(v.data_cupom as date) = '20221107'

🌐
Oozou
oozou.com › til › postgresql-update-join-64
PostgreSQL UPDATE Join | OOZOU
This query shows how to use the PostgreSQL UPDATE join syntax to update data in a table based on values in another table. for example; updating subscription_missions.giftbox_type with rewards.gift_box_type through mission_rewards table (tested on 300k records, crazy level fast)
🌐
Vela
vela.simplyblock.io › postgresql tutorial › modifying data › update join
UPDATE Join in PostgreSQL | PostgreSQL Tutorial
April 7, 2026 - PostgreSQL UPDATE join uses the FROM clause to join a second table and update the first based on matching values. Write UPDATE target SET col = source.col FROM source WHERE target.join_col = source.join_col — there is no JOIN keyword in this ...
🌐
Verve AI
vervecopilot.com › interview-questions › why-mastering-postgres-update-using-join-is-crucial-for-efficient-database-management
Why Mastering Postgres Update Using Join Is Crucial For Efficient… · Crucial For Efficient Database Mana… · Interview Q&A | Verve AI
This reduces the number of rows PostgreSQL needs to process, even before the join. 4. Consider Temporary Tables for Complex Joins: For extremely complex joins involving many tables or large intermediate results, creating a temporary table with the pre-joined and pre-filtered data might sometimes be more efficient. You then perform a simple `UPDATE ... FROM` against this smaller temporary table. 5. Use `EXPLAIN ANALYZE`: Always use `EXPLAIN ANALYZE` to understand the query plan of your `postgres update using join` statement.
🌐
PostgreSQL Tutorial
postgresqltutorial.com › home › postgresql tutorial › postgresql update join
PostgreSQL UPDATE Join with A Practical Example
February 1, 2024 - Sometimes, you need to update data in a table based on values in another table. In this case, you can use the PostgreSQL UPDATE join. Here’s the basic syntax of the UPDATE join statement: UPDATE table1 SET table1.c1 = new_value FROM table2 WHERE table1.c2 = table2.c2;Code language: SQL (Structured Query Language) (sql)
🌐
DataCamp
datacamp.com › tutorial › sql-update-with-join
SQL UPDATE with JOIN: How it Works | DataCamp
September 11, 2024 - Large tables/datasets may affect the query performance of UPDATE with JOIN since it requires more processing power. The UPDATE with JOIN concept works in SQL Server, MySQL, and PostgreSQL, although the syntax differs depending on the database.