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
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 ;
🌐
PostgreSQL
postgresql.org › docs › current › sql-update.html
PostgreSQL: Documentation: 18: UPDATE
May 14, 2026 - While there is no LIMIT clause for UPDATE, it is possible to get a similar effect through the use of a Common Table Expression and a self-join. With the standard PostgreSQL table access method, a self-join on the system column ctid is very efficient: WITH exceeded_max_retries AS ( SELECT w.ctid FROM work_item AS w WHERE w.status = 'active' AND w.num_retries > 10 ORDER BY w.retry_timestamp FOR UPDATE LIMIT 5000 ) UPDATE work_item SET status = 'failed' FROM exceeded_max_retries AS emr WHERE work_item.ctid = emr.ctid;
Discussions

sql - updating table rows in postgres using subquery - Stack Overflow
I want to update the table. Initially i tested my query using this insert statement: insert into address customer,supplier,partner SELECT case when cust.addr1 is not null then TRUE else FALSE end customer, case when suppl.addr1 is not null then TRUE else FALSE end supplier, case when partn.addr1 is not null then TRUE else FALSE end partner from ... More on stackoverflow.com
🌐 stackoverflow.com
postgresql - Postgres SELECT ... FOR UPDATE in functions - Stack Overflow
0 Atomically retrieve batch of rows from Postgresql database · 1 How can I update multiple items from a select query in Postgres? More on stackoverflow.com
🌐 stackoverflow.com
Updating table with results of a Select query
Why would you want to do this? You are creating duplicate data in your database and are breaking normalization rules. I think you should rethink this… Think about the scenario where a customer changes types. Now you have to go touch a 25M row table and update thousands of rows potentially instead of one column in one row. More on reddit.com
🌐 r/SQL
11
6
September 17, 2024
Unit testing `FOR UPDATE SKIP LOCKED`
It seems like you are on the right track: you are going to have to control the schedule that queries are submitted across multiple connections. However, I don't see why you need multiple threads, just multiple connections. More on reddit.com
🌐 r/PostgreSQL
5
2
March 3, 2020
🌐
w3resource
w3resource.com › PostgreSQL › snippets › postgresql-update-from-select.php
PostgreSQL Update from Select with Examples and Explanation
December 31, 2024 - The salary column in employees is adjusted using the bonus_percent from the departments table. Rows in employees are matched with rows in departments based on department_id. ... -- Adjust salary using a subquery UPDATE employees SET salary = salary + ( SELECT salary * bonus_percent FROM departments WHERE employees.department_id = departments.id );
🌐
W3Schools
w3schools.com › postgresql › postgresql_update.php
PostgreSQL - The UPDATE Statement
PostgreSQL CREATE TABLE PostgreSQL INSERT INTO PostgreSQL Fetch Data PostgreSQL ADD COLUMN PostgreSQL UPDATE PostgreSQL ALTER COLUMN PostgreSQL DROP COLUMN PostgreSQL DELETE PostgreSQL DROP TABLE Create Demo Database · PostgreSQL Operators PostgreSQL SELECT PostgreSQL SELECT DISTINCT PostgreSQL ...
🌐
Neon
neon.com › postgresql › tutorial › update-join
PostgreSQL UPDATE Join with Practical Examples
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;
🌐
PostgreSQL
postgresql.org › docs › current › sql-select.html
PostgreSQL: Documentation: 18: SELECT
May 14, 2026 - PostgreSQL allows it in any SELECT query as well as in sub-SELECTs, but this is an extension. The FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE variants, as well as the NOWAIT and SKIP LOCKED options, do not appear in the standard. PostgreSQL allows INSERT, UPDATE, DELETE, and MERGE to be used as WITH queries. This is not found in the SQL standard. DISTINCT ON ( ... ) is an extension of the SQL standard. ROWS FROM( ...
Find elsewhere
🌐
Medium
dgursoy.medium.com › select-for-update-in-postgres-1b21f0a1b8c3
Select For Update in Postgres. The SELECT FOR UPDATE statement enables… | by Deniz GÜRSOY | Medium
June 24, 2025 - The name SELECT FOR UPDATE clearly indicates its purpose: it tells PostgreSQL, “I intend to select these rows because I plan to update them shortly; therefore, please block other transactions from modifying this data.”
🌐
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.
🌐
CommandPrompt Inc.
commandprompt.com › education › how-to-use-update-query-in-postgresql
How to Use Update Query in PostgreSQL — CommandPrompt Inc.
August 2, 2022 - In PostgreSQL, the UPDATE query is used with the assistance of the SET clause to update or modify the table’s record/data.
🌐
Databasefaqs
databasefaqs.com › home › how to write update query in postgresql
How to write update query in PostgreSQL - DatabaseFAQs.com
July 4, 2025 - You can update or modify a single record using the PostgreSQL update query. Suppose you have a table called ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’, which is shown below. Now, you need to update the country for the customer ‘Emma’ from Canada to the USA. You can use the command below. UPDATE customers SET country = 'USA' WHERE id = 2; SELECT * FROM customers;
🌐
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): PostgreSQLs 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...
🌐
TIL
ryanguill.com › postgresql › sql › 2017 › 05 › 18 › postgres-update-with-select.html
Postgres update using subselect - TIL
For example: UPDATE table SET (foo, bar) = (SELECT 'foo', 'bar') WHERE id = 1; The subselect can be any query, just return your columns in the same order as the column list you provide.
🌐
DataCamp
datacamp.com › doc › postgresql › update
PostgreSQL UPDATE
UPDATE customers SET status = 'inactive' WHERE last_order_date < (SELECT NOW() - INTERVAL '1 year');
🌐
DB Vis
dbvis.com › thetable › the-postgres-update-statement-a-deep-dive
The Postgres UPDATE Statement: A Deep Dive
September 5, 2024 - In this technical article, we will look into the Postgres UPDATE statement, and understand its statement syntax. From updating single and multiple rows to incorporating conditions, and expressions, we will learn the ins and outs behind data modification in PostgreSQL.
🌐
Cockroach Labs
cockroachlabs.com › home › resources › blog › what is select for update in sql (with examples)?
SELECT FOR UPDATE in SQL: How Row Locking Works
A complete transaction that uses SELECT FOR UPDATE on that table could look like this: BEGIN; SELECT * FROM kv WHERE k = 1 FOR UPDATE; UPDATE kv SET v = v + 5 WHERE k = 1; COMMIT;
🌐
PostgreSQL
postgresql.org › docs › 9.0 › sql-select.html
PostgreSQL: Documentation: 9.0: SELECT
July 6, 2016 - Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows, SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted).
🌐
Haril
haril.dev › en › blog › 2024 › 04 › 20 › select-for-update-in-PostgreSQL
How SELECT FOR UPDATE Works | HARIL
December 28, 2025 - Even if Transaction A locks a row using select for update, Transaction B can still read the data. Reading without locks. Until Transaction A commits, Transaction B cannot modify the data. If Transaction B is waiting for Transaction A to commit, as soon as Transaction A commits, Transaction B can proceed immediately. Consistent reading is not guaranteed. Loss of updates can occur. The area with an Exclusive Lock is highlighted in bold. As you can see from the diagram, PostgreSQL does not prevent reading without locks.