时间戳

首页 > 解决方案 > 如何使用临时表中的列进行 MSQL 中的以下更新

mysql - 如何使用临时表中的列进行 MSQL 中的以下更新

问题描述

我有一个 MySQL 数据库。我想使用其他表中 concat 列中的值更新表中的列（在我的情况下是 bms_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。

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

在此处输入图像描述

我努力了

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;

但是得到了同样的错误。

标签： mysql

解决方案

您需要加入 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;

推荐阅读