我正在尝试创建一个更新另一个表的查询，但我将使用的SUBQUERY/DERIVED-query要求我将它们设置为GROUPBY和GROUP_CONCAT（）。
我能够得到我想要的输出，但要做到这一点，我必须创建一个临时表来存储“分组/连接”数据，然后将“重新组织”的数据推送到目标表。要做到这一点，我必须运行两个单独的查询——一个用字段中的“有组织”数据填充临时表，然后运行另一个UPDATE，将“有组织的”数据从临时表推送到最终目标表。
我创建了一个REPREX，它体现了我试图实现的目标：

/*
Create a simplified sample table:

* /

CREATE TABLE `test_tbl` (
            `equipment_num` varchar(20),
            `item_id` varchar(40),
            `quantity` decimal(10,2),
            `po_num` varchar(20)
)
--
-- Dumping data for table `test_tbl`
--

INSERT INTO `test_tbl` (`equipment_num`, `item_id`, `quantity`, `po_num`) VALUES
(TRHU8399302, '70-8491', '5.00', 'PO10813-Air'),
(TRHU8399302, '40-21-72194', '22.00', '53841'),
(TRHU8399302, '741-PremBundle-CK', '130.00', 'NECTAR-PMBUNDLE-2022'),
(TRHU8399302, '741-GWPBundle-KG', '650.00', 'NECTAR2021MH185-Fort'),
(TRHU6669420, '01-DGCOOL250FJ', '76000.00', '4467'),
(TRHU6669420, '20-2649', '450.00', 'PO9994'),
(TRHU6669420, 'PFL-PC-GRY-KG', '80.00', '1020'),
(TRHU6669420, '844067025947', '120.00', 'Cmax 2 15 22'),
(TRHU5614145, 'Classic Lounge Chair Walnut leg- A XH301', '372.00', 'P295'),
(TRHU5614145, '40-21-72194', '22.00', '53837'),
(TRHU5614145, 'MAR-PLW-55K-BX', '2313.00', 'SF220914R-CA'),
(TRHU5614145, 'OPCP-BH1-L', '150.00', 'PO-00000429B'),
(TRHU5367889, 'NL1000WHT', '3240.00', 'PO1002050'),
(TRHU4692842, '1300828', '500.00', '4500342008'),
(TRHU4560701, 'TSFP-HB2-T', '630.00', 'PO-00000485A'),
(TRHU4319443, 'BGS21ASFD', '20.00', 'PO10456-1'),
(TRHU4317564, 'CSMN-AM1-X', '1000.00', 'PO-00000446'),
(TRHU4249449, '4312970', '3240.00', '4550735164'),
(TRHU4238260, '741-GWPBundle-TW', '170.00', 'NECTAR2022MH241'),
(TRHU3335270, '1301291', '60000.00', '4500330599'),
(TRHU3070607, '36082233', '150.00', '11199460'),
(TLLU8519560, 'BGM03AWFX', '360.00', 'PO10181A'),
(TLLU8519560, '10-1067', '9120.00', 'PO10396'),
(TLLU8519560, 'LUNA-KP-SS', '8704.00', '4782'),
(TLLU5819760, 'GS-1319', '10000.00', '62719'),
(TLLU5819760, '2020124775', '340.00', '3483'),
(TLLU5389611, '1049243', '63200.00', '4500343723'),
(TLLU4920852, '40-21-72194', '22.00', '53839'),
(TRHU3335270, '4312904', '1050.00', '4550694829'),
(TLLU4540955, '062-06-4580', '86.00', '1002529'),
(TRHU3335270, 'BGM03AWFK', '1000.00', 'PO9912'),
(TLLU4196942, 'Classic Dining Chair,Walnut Legs, SF XH1', '3290.00', 'P279'),
(TLLU4196942, 'BGM61AWFF', '852.00', 'PO10365');

---
--- The data above is a subsample of what I have on the db, what I'm trying to do is to update another table based off this info but with some GROUP_CONCAT()
--- With the data from above, I need to GROUP_CONCAT(item_id),GROUP_CONCAT(quantity), GROUP_CONCAT(po_num) -- grouping by equipment_num field.
---
--- What I'm attempting to do is to do an UPDATE to another table with the GROUPED by equipment_num with and the Group_concats for the fields described above.
--- 
--- The only way I was able to do what I desired was with a intermediary TEMPORARY table.
---

--- Create the temp table:
--- Since what I need is a "list" of the quantities, I had to do a GROUP_CONCAT(CONCAT(quantity,''))

DROP TABLE __tmp__; CREATE TABLE __tmp__
SELECT equipment_num, GROUP_CONCAT( item_id ), GROUP_CONCAT(CONCAT(  quantity ,  '' ) ), GROUP_CONCAT( po_num )
FROM  `test_tbl`
GROUP BY equipment_num

--- Then FINALLY pull the information in the format I desire to the destination table:

UPDATE `dest_tbl` AS ms INNER JOIN `__tmp__` AS isn ON ( ms.equipment_num = isn.equipment_num ) SET ms.item_id = isn.item_id,
ms.piece_count = isn.quantity,
ms.pieces_detail = isn.po_num

我正在尝试创建一个查询，它生成一个派生查询，完成group_concat部分，然后将派生查询结果推送到最终的目标表。
任何建议都将不胜感激。
谢谢你抽出时间。
结核病。
编辑：谢谢你给我的回复，但我正在努力避免使用临时表。
我试图避免创建临时表……我想知道如何一次完成。。。
我在想一些事情，大致如下：

UPDATE dest
INNER JOIN(
    SELECT src.equipment_num, GROUP_CONCAT(src.item_id) as item_id, 
        GROUP_CONCAT(CONCAT(src.quantity)) as quantity, 
        GROUP_CONCAT(src.po_num) as po_num
    FROM  `item_shipped_ns` as src
    INNER JOIN milestone_test_20221019 as dest ON(src.equipment_num=dest.equipment_num)
    WHERE src.importer_id='123456'
    GROUP BY src.equipment_num
) as tmp ON(src.equipment_num=tmp.equipment_num)

SET 
dest.item_num=tmp.item_id,
dest.piece_count=tmp.quantity,
dest.pieces_detail=tmp.po_num;

不幸的是，上面的方法不起作用，我得到了下面的错误消息。

1146-表“fgcloud”。dest“不存在

编辑2：我在上面缺少了一个括号，这导致了一个不同的错误，我已经修复了它，但表别名有问题。应该更新的问题表是“milestone_test_20221019”-它被声明为“dest”，但我说它找不到它，建议吗？更新“milestone_test_20221019”之前，我需要获取信息和聚合的源表是“item_shipped_ns”，我相信“tmp”表是派生/子查询表别名。。。