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

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.

CodePudding user response：

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;

CodePudding user response：

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;