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.