Azhar92 Azhar92 - 9 days ago 5
MySQL Question

Update rows from table using condition present in another far-linked table

I'm fairly new to MySQL and wanted some assistance in the below mentioned scenario :

I have 4 Tables :
1) Book
2) Chapters
3) Subchapters
4) Question

Now, Question is linked with Subchapter via foreign key. Similarly Subchapter is linked with Chapter. And Chapter is linked with Book.

What I want to do is 'update' some rows in 'Question' table which belong to let's say 'Book' with id = 5. Since 'Question' is not directly linked with 'Book', I don't know how to go with the update.

How do I go about doing this?

Thanks

Answer

You'll need to JOIN in the right rows from your table with a WHERE condition I'm pretty sure:

UPDATE Questions SET your_col = /* something */

JOIN Subchapter
Subchapter.id = Question.subchapter_id

JOIN Chapter ON
Chapter.id = Subchapter.chapter_id

JOIN Book ON
Book.id = Chapter.book_id

WHERE Book.id = 5
Comments