UPDATE statement using GROUP_CONCAT()

Question

I'm attempting to create a single query that UPDDATES another table where the SUBQUERY/ DERIVED-QUERY uses two functions, GROUP BY and GROUP_CONCAT().

I'm able to get my desired output if I utilize a intermediate temporary table to store the "grouped/ concated" data and then push that "re-organized" data to the destination table.

I have to run 2 separate queries--one that populates the temp table with the "organized" data in its fields and then another UPDATE that pushes the "organized" data from the temp table to the final destination table.

I use implicit-explicit casting when I use GROUP_CONCAT(CONCAT(quantity,'')).

CREATE TABLE `test_tbl` (
            `equipment_num` varchar(20),
            `item_id` varchar(40),
            `quantity` decimal(10,2),
            `po_num` varchar(20)
)

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');

I'm trying to update another table based off this information but with GROUP_CONCAT().

GROUP_CONCAT(item_id),
GROUP_CONCAT(quantity),
GROUP_CONCAT(po_num) -- grouping by equipment_num field.

I UPDATE another table with the GROUPED by equipment_num with and the Group_concats for the fields described above. I was able to do what I desired with a intermediary TEMPORARY table. Since what I need is a list of the quantities, I do 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

Finally I 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

What is a single query that generates a derived query that does the group_concat part and then pushes that derived query result to the final destination table?

I'm trying to AVOID using the temp table. I was thinking:

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 - Table 'fgcloud.dest' doesn't exist

I'm having issues with the table aliases. The table that should be updated is the milestone_test_20221019--it is declared as dest, yet it cannot find it. The source table to get the information and to aggregate before updating milestone_test_20221019 is item_shipped_ns and that tmp table is the derived/sub-query table alias.

`UPDATE dest` should be `UPDATE milestone_test_20221019 AS dest` — Barmar, Oct 20 '22 at 19:46
I don't see why you have this table in the subquery. You only need to join with the destination table in the main query, not the subquery. — Barmar, Oct 20 '22 at 19:47
This post is not clear about what code is getting what error. Please ask 1 question about 1 wrong query/function. Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Please clarify via edits, not comments. Please avoid social & meta commentary. The "minimal" in [mre] includes the example data. — philipxy, Mar 14 '23 at 23:53
@philipxy hi. that's the whole thing of this post... I explain what I'm trying to achieve and which was CREATE A SUBQUERY that uses GROUP_CONCAT and GROUP BY as part of it. Now the thing that bothers me is, the people that ACTUALLY responded understood it and drove me to actually got the ANSWER figured out and I posted it at the end. Now I'm here trying to figure out how to "fix" a post so it is to an "admins" standard. Because it is NOT clear to them. And I'm stuck out of being able to post questions until I fix these... Which is absurd... This is asking ppl to create new accounts. — Technobrat, Mar 15 '23 at 00:09
Please edit the presentation to be clear. This site is a collection of helpful Q&A & an unclear presentation is not helpful & lack of clarity in a presentation is not a consequence of having the problem you are stuck in. [ask] [Help] [tour] [meta] [meta.se] People responding doesn't make a post good & the large number of comments & edits reflects the poor organization of the post. PS Please flag & delete obsolete comments. — philipxy, Mar 15 '23 at 00:12

Barmar · Answer 1 · 2022-10-20T19:50:40.580

You need to give an alias to the GROUP_CONCAT() so you'll get a column named item_id. It won't use the argument to GROUP_CONCAT() as the name of the resulting column automatically.

CREATE TABLE __tmp__
SELECT equipment_num, 
    GROUP_CONCAT( item_id ) AS item_id, 
    GROUP_CONCAT( quantity ) AS quantity, 
    GROUP_CONCAT( po_num ) AS po_num
FROM  `test_tbl`
GROUP BY equipment_num

To do this in a single query without creating the __tmp__ table, just put the query used to create __tmp__ in a subquery in the UPDATE.

UPDATE milestone_test_20221019 AS dest
JOIN (
    SELECT equipment_num, 
        GROUP_CONCAT( item_id ) AS item_id, 
        GROUP_CONCAT( quantity ) AS quantity, 
        GROUP_CONCAT( po_num ) AS po_num
    FROM  item_shipped_ns
    GROUP BY equipment_num
) AS src ON dest.equipment_num = src.equipment_num
SET dest.item_id = src.item_id,
    dest.quantity = src.quantity,
    dest.po_num = src.po_num

One can also name the columns before the SELECT. Example: `CREATE TABLE NAME (... column definitions ...) SELECT ...` Cf. https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html — Bill Karwin, Oct 19 '22 at 22:53
Yes, one can, but unless you need to specify data types, indexes, etc. this is simpler. — Barmar, Oct 19 '22 at 22:53

score 0 · Accepted Answer · edited Mar 15 '23 at 00:07

Below is an example of how to use an UPDATE with GROUP_CONCAT() as well an implicit-explicit casting for the quantity field.

UPDATE milestone_test_20221019 as 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 t1
    ON (src.equipment_num=t1.equipment_num)
    WHERE src.importer_id='4081836'
    GROUP BY src.equipment_num
    ) AS tmp
ON (tmp.equipment_num=dest.equipment_num)
SET 
dest.item_num=tmp.item_id,
dest.piece_count=tmp.quantity,
dest.pieces_detail=tmp.po_num;

UPDATE statement using GROUP_CONCAT()

2 Answers2