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 ExchangePostgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
You're after the UPDATE FROM syntax.
UPDATE
table T1
SET
column1 = T2.column1
FROM
table T2
INNER JOIN table T3 USING (column2)
WHERE
T1.column2 = T2.column2;
References
- Code sample here: GROUP BY in UPDATE FROM clause
- And here
- Formal Syntax Specification
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;
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.