如何用含GROUP_CONCAT的单条UPDATE语句替代临时表更新目标表

问题背景 #

需要从test_tbl按equipment_num分组，将GROUP_CONCAT(item_id)、GROUP_CONCAT(quantity)、GROUP_CONCAT(po_num)的结果直接更新到目标表，无需临时表，之前尝试的语句因表别名报错#1146 - Table 'fgcloud.dest' doesn't exist。

解决方案 #

假设目标表名为dest_tbl，结构包含：

• equipment_num varchar(20)（关联字段，需保证唯一或为主键）
• item_ids text（存储拼接后的item_id）
• quantities text（存储拼接后的quantity）
• po_nums text（存储拼接后的po_num）

以下是单条UPDATE语句：

UPDATE dest_tbl dest
JOIN (
    SELECT 
        equipment_num,
        GROUP_CONCAT(item_id SEPARATOR ', ') AS item_ids,
        GROUP_CONCAT(CONCAT(quantity, '') SEPARATOR ', ') AS quantities,
        GROUP_CONCAT(po_num SEPARATOR ', ') AS po_nums
    FROM test_tbl
    GROUP BY equipment_num
) agg ON dest.equipment_num = agg.equipment_num
SET 
    dest.item_ids = agg.item_ids,
    dest.quantities = agg.quantities,
    dest.po_nums = agg.po_nums;

关键细节说明 #

• 别名规范：将分组聚合的子查询命名为agg，目标表dest_tbl命名为dest，确保JOIN时别名引用正确，避免表不存在的报错。
• 数值转字符串：用CONCAT(quantity, '')显式将decimal类型的quantity转为字符串，避免GROUP_CONCAT处理数值类型时可能出现的格式异常。
• 分隔符自定义：SEPARATOR ', '指定拼接的分隔符，可根据需求修改为其他符号（如;），不指定则默认使用逗号。
• 关联有效性：必须保证dest_tbl和test_tbl的equipment_num能正确匹配，否则无法完成对应记录的更新。

如果需要同时处理新增和更新（即test_tbl中有但dest_tbl没有的equipment_num也能写入），可以改用INSERT ... ON DUPLICATE KEY UPDATE语句：

INSERT INTO dest_tbl (equipment_num, item_ids, quantities, po_nums)
SELECT 
    equipment_num,
    GROUP_CONCAT(item_id SEPARATOR ', '),
    GROUP_CONCAT(CONCAT(quantity, '') SEPARATOR ', '),
    GROUP_CONCAT(po_num SEPARATOR ', ')
FROM test_tbl
GROUP BY equipment_num
ON DUPLICATE KEY UPDATE
    item_ids = VALUES(item_ids),
    quantities = VALUES(quantities),
    po_nums = VALUES(po_nums);

此语句会插入新记录，若equipment_num已存在则更新对应字段，需确保dest_tbl的equipment_num是唯一键或主键。

内容的提问来源于stack exchange，提问作者Technobrat