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!

Answer 1

• 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`