The problem with your query is that the subquery is not correlated. You dodn't need and you shouldn't use lessons inside the subquery.

UPDATE lessons
SET minicourse_id = subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  WHERE topics.id = lessons.topic_id   -- this refers to the "lesson"
                                       -- of the main query
) AS subquery ;

In fact, it can be written without a subquery at all:

UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

or with a different subquery:

UPDATE lessons 
SET minicourse_id 
    = ( SELECT minicourse_id 
        FROM topics 
        WHERE id = lessons.topic_id
      ) ;

Regarding your design, I assume you have added a lessons.minicourse_id column and this foreign key:

ALTER TABLE lessons
    ADD minicourse_id INT ;

ALTER TABLE lessons
    ADD FOREIGN KEY (minicourse_id) 
        REFERENCES minicourses (id) ;

While this achieves what you want, there is a small issue: you may end up with rows in child (lessons) that refer to a grandparent (minicourse) A and also to a parent (topics) that refers to a different grandparent B.

Of course if all your applications and users have code that is correct, this won't happen. But I suggest you enforce this in the database level and not (only) in the application level. This is quite easy to do, with the following change in the foreign keys.

It basically sets the FK to minicourses to be "through" lessons, without a direct FK. You'll still be able to use direct joins between lessons and minicourses:

ALTER TABLE lessons
    ADD minicourse_id INT ;

-- the UPDATE is the same!
UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

ALTER TABLE lessons
    ALTER minicourse_id SET NOT NULL ;

-- we need this for the FK below
ALTER TABLE topics 
    ADD CONSTRAINT minicourse_topic_UQ 
        UNIQUE (minicourse_id, id) ;

-- the FK is "lessons -> topics"
ALTER TABLE lessons
    ADD CONSTRAINT lessons_to_topics_FK2
        FOREIGN KEY (minicourse_id, topic_id) 
        REFERENCES topics (minicourse_id, id) ;

-- drop the previous FK to topics
ALTER TABLE lessons
    DROP CONSTRAINT lessons_to_topics_FK ;
Answer from ypercubeᵀᴹ on Stack Exchange
🌐
PostgreSQL
postgresql.org › docs › current › sql-update.html
PostgreSQL: Documentation: 18: UPDATE
May 14, 2026 - Documentation → PostgreSQL 18 · Supported Versions: Current (18) / 17 / 16 / 15 / 14 · Development Versions: 19 / devel · Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 · UPDATE — update rows of a table · [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ] { * | output_expression [ [ AS ] output_name ] } [, ...] ] UPDATE changes the values of the specified columns in all rows that satisfy the condition.
🌐
W3Schools
w3schools.com › postgresql › postgresql_update.php
PostgreSQL - The UPDATE Statement
PostgreSQL Operators PostgreSQL ... ANY PostgreSQL ALL PostgreSQL CASE ... The UPDATE statement is used to modify the value(s) in existing records in a table....
🌐
TigerData
tigerdata.com › learn › using-postgresql-update-with-join
Using PostgreSQL UPDATE With JOIN | Tiger Data
December 10, 2025 - This query would mark any product as inactive if it has at least one inventory record with zero quantity. Important note: When using UPDATE with FROM in PostgreSQL, if multiple rows in the joined table (inventory) match a single row in the target table (products), the target row will be updated ...
🌐
GeeksforGeeks
geeksforgeeks.org › postgresql › postgresql-update
PostgreSQL UPDATE Statement - GeeksforGeeks
July 12, 2025 - This ensures that only the row with the matching first name "Raju" is updated, demonstrating the use of the WHERE clause to target specific rows in the PostgreSQL UPDATE statement. ... This query updates a single row in the employee table, modifying the value of the last_name column where the first name is 'Raju'.
Top answer
1 of 1
7

The problem with your query is that the subquery is not correlated. You dodn't need and you shouldn't use lessons inside the subquery.

UPDATE lessons
SET minicourse_id = subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  WHERE topics.id = lessons.topic_id   -- this refers to the "lesson"
                                       -- of the main query
) AS subquery ;

In fact, it can be written without a subquery at all:

UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

or with a different subquery:

UPDATE lessons 
SET minicourse_id 
    = ( SELECT minicourse_id 
        FROM topics 
        WHERE id = lessons.topic_id
      ) ;

Regarding your design, I assume you have added a lessons.minicourse_id column and this foreign key:

ALTER TABLE lessons
    ADD minicourse_id INT ;

ALTER TABLE lessons
    ADD FOREIGN KEY (minicourse_id) 
        REFERENCES minicourses (id) ;

While this achieves what you want, there is a small issue: you may end up with rows in child (lessons) that refer to a grandparent (minicourse) A and also to a parent (topics) that refers to a different grandparent B.

Of course if all your applications and users have code that is correct, this won't happen. But I suggest you enforce this in the database level and not (only) in the application level. This is quite easy to do, with the following change in the foreign keys.

It basically sets the FK to minicourses to be "through" lessons, without a direct FK. You'll still be able to use direct joins between lessons and minicourses:

ALTER TABLE lessons
    ADD minicourse_id INT ;

-- the UPDATE is the same!
UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

ALTER TABLE lessons
    ALTER minicourse_id SET NOT NULL ;

-- we need this for the FK below
ALTER TABLE topics 
    ADD CONSTRAINT minicourse_topic_UQ 
        UNIQUE (minicourse_id, id) ;

-- the FK is "lessons -> topics"
ALTER TABLE lessons
    ADD CONSTRAINT lessons_to_topics_FK2
        FOREIGN KEY (minicourse_id, topic_id) 
        REFERENCES topics (minicourse_id, id) ;

-- drop the previous FK to topics
ALTER TABLE lessons
    DROP CONSTRAINT lessons_to_topics_FK ;
🌐
DataCamp
datacamp.com › doc › postgresql › update
PostgreSQL UPDATE
In this syntax, `UPDATE table_name` specifies the table to modify, and `SET` assigns new values to the specified columns, optionally filtered by a `WHERE` condition.
Find elsewhere
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.

🌐
DB Vis
dbvis.com › thetable › the-postgres-update-statement-a-deep-dive
The Postgres UPDATE Statement: A Deep Dive
September 5, 2024 - Omit the WHERE clause in a Postgres UPDATE query to update every row in the table.
🌐
TutorialsPoint
tutorialspoint.com › postgresql › postgresql_update_query.htm
PostgreSQL - UPDATE
In PostgreSQL, UPDATE statement is used to modify or change the existing records in a table. You can use WHERE clause with UPDATE statement to update the selected rows. Otherwise, all the rows would be updated.
🌐
Medium
medium.com › @mnu › update-a-postgresql-table-using-a-with-query-648eefaae2a6
Update a PostgreSQL table using a WITH query | by m4nu56 | Medium
October 10, 2020 - I often need to update a table using values from a different table and most of the time the quickest and also dirtiest solution is simply to do a subquery. A better and cleaner solution is to use the UPDATE ..
🌐
CastorDoc
castordoc.com › how-to › how-to-use-update-in-postgresql
How to use update in PostgreSQL?
When writing an Update query in PostgreSQL, it's important to define the table and column names correctly.
🌐
PostgreSQL
postgresql.jp › docs › 8.0 › sql-update.html
UPDATE
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
🌐
MariaDB
mariadb.org › home
MariaDB Foundation - MariaDB.org
November 13, 2019 - It is built upon the values of performance, stability, and openness, and MariaDB Foundation ensures contributions will be accepted on technical merit. Recent new functionality includes advanced clustering with Galera Cluster 4, compatibility features with Oracle Database and Temporal Data Tables, allowing one to query the data as it stood at any point in the past.
🌐
Tibco
docs.tibco.com › pub › tci-flogo-payg › 2.11.0 › doc › html › GUID-CEC78B99-3730-4C4E-99DB-0E0D769D1469.html
PostgreSQL Update
Substitution parameters from the query statement are provided in the Parameters node. The Update activity returns the number of rows affected by the query.
🌐
Quora
quora.com › How-do-you-update-the-PostgreSQL-field-with-results-from-another-query-PostgreSQL-SQL-update-development
How to update the PostgreSQL field with results from another query (PostgreSQL, SQL update, development) - Quora
Answer (1 of 2): PostgreSQL’s UPDATE command allows the SET clause to read from a SELECT sub-query. This sub-query can be another table, or the same table. * PostgreSQL UPDATE Syntax Example This ADDRESS table has known ZIP codes, but the STATE_CODE is blank or uncertain. Use a correlated sub...
🌐
GeeksforGeeks
geeksforgeeks.org › python › python-postgresql-update-table
Python PostgreSQL - Update Table - GeeksforGeeks
December 7, 2021 - Here we are going to see how to update the columns of the table. The table after modification looks like the table shown below. As we can see for every tuple state value is changed to Kashmir. ... # importing psycopg2 module import psycopg2 # establishing the connection conn = psycopg2.connect( database="postgres", user='postgres', password='password', host='localhost', port= '5432' ) # creating a cursor object cursor = conn.cursor() # query to update table with where clause sql='''update Geeks set state='Kashmir'; ''' # execute the query cursor.execute(sql) print('table updated..') print('table after updation...') sql2='''select * from Geeks;''' cursor.execute(sql2); # print table after modification print(cursor.fetchall()) # Commit your changes in the database conn.commit() # Closing the connection conn.close()# code
🌐
PostgreSQL
docs.postgresql.fr › 9.6 › sql-update.html
UPDATE
UPDATE films SET genre = 'Dramatic' WHERE CURRENT OF c_films; Cette commande est conforme au standard SQL, à l'exception des clauses FROM et RETURNING qui sont des extensions PostgreSQL™.
🌐
TechOnTheNet
techonthenet.com › postgresql › update.php
PostgreSQL: UPDATE Statement
This PostgreSQL UPDATE example would update the first_name to the default value for the field in the contacts table where the contact_id is 35.
🌐
Tutorial Teacher
tutorialsteacher.com › postgresql › update-data
PostgreSQL Update Data in a Table
Use the UPDATE statement to modify existing data in the table. The UPDATE statement only updates data in the table and does not modify the structure of a table.