MySQL UPDATE with GROUP_CONCAT joining three tables

Question

The goal: update T1 with GROUP_CONCAT info from T3 with JOIN across T2.

Here’s a simplified version of the table structures:

T1: xfer_lectures
Relevant fields: lecture_id, topics
(I'm trying to fill the topics field with a concatenated list of topics designated for that lecture.)

T2: calendar_lecture_topics
Relevant fields: event_id, topic_id
(T1.lecture_id = T2.event_id)

T3: lecture_topics
Relevant fields: id, title
(T2.topic_id = T3.event_id)

I can successfully SELECT the info I want with the following query:

SELECT 
    T1.`lecture_id`, GROUP_CONCAT(DISTINCT `title` SEPARATOR '; '), COUNT(*)
FROM
    `xfer_lectures` T1
    INNER JOIN
        `calendar_lecture_topics` T2
    INNER JOIN
        `lecture_topics` T3 
    ON T1.`lecture_id` = T2.`event_id`
    AND T2.`topic_id` = T3.`id`
    GROUP BY T1.`lecture_id`

However, when I try to UPDATE T1 with the concatenated info, I fail. I’ve tried a bunch of versions of the update query, most of which yield errors. This one runs as a valid query but fills every topic field with the same list of ALL the topics in the topics table:

 UPDATE 
    `xfer_lectures` T1
 JOIN `calendar_lecture_topics` T2
    ON T1.`lecture_id`=T2.`event_id`
 JOIN `lecture_topics` T3
    ON T2.`topic_id` = T3.`id`
 SET T1.`topics` = (
    SELECT 
    GROUP_CONCAT(`title` SEPARATOR '; ')
    FROM `lecture_topics`
    )

I also tried versions where the SELECT statement includes a GROUP_BY clause, but I still ended up with the same list of topics for every record as opposed to the two or three relevant topics per lecture. For example:

SET T1.`topics` = (       
SELECT        
GROUP_CONCAT(`title` SEPARATOR '; ')       
FROM `lecture_topics`
WHERE T2.`topic_id` = T3.`id` 
AND T1.`lecture_id`=T2.`event_id`
GROUP BY T2.`event_id`)

Where am I going wrong? I'm not terribly experienced with complex queried so my understanding of both JOINs and grouping is probably flawed. Any assistance will be much appreciated!

I think you have a typo: It should be: `ON T1.lecture_id=T2.event_id`, in your JOIN condition between T1 and T2 — Madhur Bhaiya, Oct 04 '18 at 13:08
Thanks for catching my typo, Madhur! I found a couple more that I've corrected in addition. I had changed some table names along the way and mistyped the queries in my original question. Unfortunately the syntax is still wrong, so while the corrected version will hopefully make the question clearer, the problem remains. — codebird, Oct 04 '18 at 16:29

Madhur Bhaiya · Accepted Answer · 2018-10-04T16:56:10.180

SELECT GROUP_CONCAT(title SEPARATOR '; ') FROM lecture_topics) will basically return ALL the titles from the lecture_topics table in a string. That is why your SET query is updating all the lectures with same string (containing all the titles)
You need to basically use a Derived Table here. In this derived table, you get titles based on the grouping of event_id (lecture_id).
Now, join this table with xfer_lectures on event_id = lecture_id, and use the Group_concat() result from the derived table to update the values in xfer_lectures table.

Try this:

 UPDATE 
    `xfer_lectures` AS T1
 JOIN ( SELECT 
          T2.`event_id`, 
          GROUP_CONCAT(T3.`title` SEPARATOR '; ') as `topics`
        FROM `calendar_lecture_topics` AS T2 
        JOIN `lecture_topics` AS T3 
          ON T2.`topic_id` = T3.`id` 
        GROUP BY T2.`event_id`
       ) AS T4 ON T4.`event_id` = T1.`lecture_id` 
 SET T1.`topics` = T4.`topics`

Bingo! Oh my gosh thank you! I never would have gotten there on my own. Greatly appreciated! I guess you'd call that nested JOINS? I clearly have some studying to do... — codebird, Oct 04 '18 at 16:56
And thanks for that link re Derived Tables. That's a great tool that I wasn't aware of. You're awesome! — codebird, Oct 04 '18 at 16:58

MySQL UPDATE with GROUP_CONCAT joining three tables

1 Answers1