如何在MSQL中为下列更新使用临时表中的列

Question

我有一个MySQL数据库。我希望使用来自其他表中的concat列的值来更新表中的列(在我的例子中是bms_title表中的title列)。

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;

但是我得到了错误: where子句中不知道的temporary_table.Textbook。

如何使用所选表中的tms_local.bms_title.thumbnail列更新CourseCode列？

在这里输入图像描述

我试过了

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;

但也有同样的错误。

Answer 1

您需要加入select语句。

如下所示：

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;

Answer 2

您的temporary_table在第一条语句和第二条语句之间丢失。

我找到了..。由于SQL结构有助于将这些结构组合在一起，因此它的可读性更强：

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;