Update Select in postgresql

Question

I have three tables, in form of grandparent, parent, and child. By this I mean a foreign key exists in child linking to parent, and another foreign key exists in parent linking to grandparent. Child and grandparent's only connection is via parent, and I currently have to join to ask questions like who are child's grandparents and who are grandparent's children.

I realised that a tiny bit of denormalisation is going to make my application code nicer to write, so, I want to add another foreign key in child, which links directly to grandparent (which never changes). This of course is redundant information, but it means I don't have to join to ask questions like give me all of child's grandparents, or give me all of grandparents grandchildren. This will make my app faster and my application code better, and I don't really need advice on the whys of doing this, but the how.

I've added the columns and I'm looking for advice on an UPDATE query to update the database with the correct values for the grandparent id in the child's new foreign key grandparent_id.

The actual tables are not grandparent, parent and child, but minicourse, topic and lesson, as below.

I tried the below query, from here, but, as I suspected, it is not quite what's needed here. The below updates all children with the same value (i.e. the wrong value, seems to be the first one it gets) of the grandparent id. I want each child updated with the correct grandparent_id. I'm rather new to operations like this in sql, so help would be much appreciated.

UPDATE lessons
SET minicourse_id=subquery.minicourse_id
FROM (
 SELECT topics.minicourse_id
 FROM topics
 JOIN lessons
 ON topics.id=lessons.topic_id
) AS subquery;

I could just do it manually (there are only about 10 minicourses) by running an update like this 10 times, having found what the values are by regular queries:

UPDATE lessons 
SET 
 minicourse_id =<I enter an actual minicourse_id here> 
WHERE 
 lesson_id in(<I enter a manually-found list of lesson_id's here>);

but I thought it could be cool to learn how to do it with sql instead.

Answer 1

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 ;

• mwal: Brilliant, thank you. I'd been very used to rhyming off select... from... but hadn't ever seen update... set... from... before. (I had update.. set.. where.. in my mental vocabulary, but not update... set... from... for some reason). Seems fairly natural :)
• ypercubeᵀᴹ: I've added a suggestion regrading your design.
• ypercubeᵀᴹ: @mwal the UPDATE is implemented almost identically by the various DBMS when it's only table. When there are joins involved, the implementations have many differences (the same UPDATE would be done differently in SQL Server, Oracle and MySQL, except perhaps my 3rd variation which would work in all I think, because it's a simple UPDATE .. SET .. WHERE .. ; with the join moved inside a subquery).
• mwal: Yes, I've add the foreign keys (also with on delete restrict which I find helpful). I'm pretty confident that differing grandparents are not going to arise, given my knowledge of its usage. So that part's probably something to postpone for the present, so as not to risk confusing myself. (is that some kind of 'double' foreign key? - didn't quite get it).
• ypercubeᵀᴹ: The term would probably be "composite FK", not double. It's more a "pass-through" foreign key. You have 1 foreign key from child to parent and 1 from parent to grandparent. Nothing else. The FK from child to grandparent is not needed because it's enforced by the other 2 FKs.