MySQL - 当 SELECT 返回多列时，根据 SELECT 的结果更新一列

Question

我读过 MySQL - UPDATE query based on SELECT Query并且正在尝试做类似的事情 - 即在表上运行 UPDATE 查询并使用 SELECT 的结果填充它。

在我的例子中，我要更新的表称为 substances 并且有一个名为 cas_html 的列，它应该将 CAS 编号(化学代码)存储为 HTML 字符串.

由于数据库的结构，我正在运行以下查询，它将为我提供物质 ID 和名称的结果集(substances.id，substances.name) 和 CAS 作为 HTML 字符串(cas_values 来自 cas.value):

SELECT s.`id`, GROUP_CONCAT(c.`value` ORDER BY c.`id` SEPARATOR '<br>') cas_values, GROUP_CONCAT(s.`name` ORDER BY s.`id`) substance_name FROM substances s LEFT JOIN cas_substances cs ON s.id = cs.substance_id LEFT JOIN cas c ON cs.cas_id = c.id GROUP BY s.id;

示例输出:

id   |   cas_values   |   substance_name
----------------------------------------
1    |   133-24<br>   |   Chemical A
         455-213<br>
         21-234
-----|----------------|-----------------
2        999-23       |   Chemical B
-----|----------------|-----------------
3    |                |   Chemical C
-----|----------------|-----------------

如您所见，cas_values 列包含 HTML 字符串(在“Chemical C”的情况下也可能是空字符串)。我想将cas_values列中的数据写入substances.cas_html。但是我无法拼凑如何执行此操作，因为我正在阅读的其他帖子在一列中获取了 UPDATE 的数据 - 我的 SELECT 返回了其他列查询。

本质上，问题是在我上面的“示例输出”表中，我返回了 3 列。其他 SO 帖子似乎只返回 1 列，这是 UPDATE 查询中使用的实际值(在本例中为 substances 表)。

这可能吗？

我正在使用 MySQL 5.5.56-MariaDB

这些是表的结构，如果这有帮助的话:

    mysql> DESCRIBE substances;
    +-------------+-----------------------+------+-----+---------+----------------+
    | Field       | Type                  | Null | Key | Default | Extra          |
    +-------------+-----------------------+------+-----+---------+----------------+
    | id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | app_id      | varchar(8)            | NO   | UNI | NULL    |                |
    | name        | varchar(1500)         | NO   |     | NULL    |                |
    | date        | date                  | NO   |     | NULL    |                |
    | cas_html    | text                  | YES  |     | NULL    |                |
    +-------------+-----------------------+------+-----+---------+----------------+
    4 rows in set (0.01 sec)

    mysql> DESCRIBE cas;
    +-------+-----------------------+------+-----+---------+----------------+
    | Field | Type                  | Null | Key | Default | Extra          |
    +-------+-----------------------+------+-----+---------+----------------+
    | id    | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | value | varchar(13)           | NO   | UNI | NULL    |                |
    +-------+-----------------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)

    mysql> DESCRIBE cas_substances;
    +--------------+-----------------------+------+-----+---------+----------------+
    | Field        | Type                  | Null | Key | Default | Extra          |
    +--------------+-----------------------+------+-----+---------+----------------+
    | id           | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
    | cas_id       | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    | substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    +--------------+-----------------------+------+-----+---------+----------------+
    3 rows in set (0.02 sec)

Answer 1

尝试这样的事情:

UPDATE substances AS s,
(
SELECT s.`id`, 
GROUP_CONCAT(c.`value` ORDER BY c.`id` SEPARATOR '<br>') cas_values,
GROUP_CONCAT(s.`name` ORDER BY s.`id`) substance_name
FROM substances s
LEFT JOIN cas_substances cs ON s.id = cs.substance_id
LEFT JOIN cas c ON cs.cas_id = c.id    
GROUP BY s.id
) AS t
SET s.cas_html=t.cas_values
WHERE s.id = t.id

如果你不想修改所有的值，限制更新来测试它的最好方法是在 where 中添加一个条件，比如这样的:

... 
WHERE s.id = t.id AND s.id = 1