how to use the column in a temporary table for the following update in MSQL

I have a MySQL database.
I want to update a column(in my case title column in bms_title table) in a table using the values from concat columns in other tables.

SELECT * FROM(SELECT distinct t.id, t.title as Textbook,
GROUP_CONCAT(concat(ci.discipline_code, ci.code, " (" , ci.type , ")") SEPARATOR ', ') as CourseCode FROM 
tms_local.bms_material m, 
tms_local.bms_title t, 
tms_local.bms_course c,
tms_local.bms_courseinfo ci
where t.id > 1 AND t.id = m.book_id 
and c.id = m.course_id 
and ci.id = c.id
and  isbn != 'NA'
GROUP BY t.id) AS temporary_table;

UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;

But I got the error: Unknow temporary_table.Textbook in where clause.

How could I update the tms_local.bms_title.thumbnail column using CourseCode column from the selected table?

enter image description here

I have tried

CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table AS (SELECT distinct t.id, t.title as Textbook,
GROUP_CONCAT(concat(ci.discipline_code, ci.code, " (" , ci.type , ")") SEPARATOR ', ') as CourseCode FROM 
tms_local.bms_material m, 
tms_local.bms_title t, 
tms_local.bms_course c,
tms_local.bms_courseinfo ci
where t.id > 1 AND t.id = m.book_id 
and c.id = m.course_id 
and ci.id = c.id
and  isbn != 'NA'
GROUP BY t.id);

UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;

But got the same error.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

you need to join the select statement.

As seen below:

UPDATE tms_local.bms_title t0
        INNER JOIN
    (SELECT 
        *
    FROM
        (SELECT DISTINCT
        t.id,
            t.title AS Textbook,
            GROUP_CONCAT(CONCAT(ci.discipline_code, ci.code, ' (', ci.type, ')')
                SEPARATOR ', ') AS CourseCode
    FROM
        tms_local.bms_material m, tms_local.bms_title t, tms_local.bms_course c, tms_local.bms_courseinfo ci
    WHERE
        t.id > 1 AND t.id = m.book_id
            AND c.id = m.course_id
            AND ci.id = c.id
            AND isbn != 'NA'
    GROUP BY t.id) AS temporary_table) t1 ON t0.title = t1.Textbook 
SET 
    t0.thumbnail = t1.ourseCode;

Method 2

Your temporary_table is being lost between the first statement and the second.

I find the WITH … AS SQL structure to be helpful to get these together and is far more readable:

WITH temporary_table AS(
    SELECT * 
    FROM(SELECT distinct t.id, 
                t.title as Textbook,
                GROUP_CONCAT(concat(ci.discipline_code, 
                                    ci.code, 
                                    " (" , 
                                    ci.type ,
                                    ")") 
                             SEPARATOR ', '
                            ) as CourseCode 
         FROM tms_local.bms_material m, 
              tms_local.bms_title t, 
              tms_local.bms_course c,
              tms_local.bms_courseinfo ci
         WHERE t.id > 1 AND t.id = m.book_id 
                    and c.id = m.course_id 
                    and ci.id = c.id
                    and  isbn != 'NA'
         GROUP BY t.id)
UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0