UPDATE statement using GROUP_CONCAT()-CodePudding

I'm attempting to create a single query that UPDATES another table but the SUBQUERY/DERIVED-QUERY that I would use requires me to have them GROUP BY and GROUP_CONCAT().

I was able to get my desired output but to do so I had to create a temporary table to store the "grouped/ concated" data and then push that "re-organized" data to the destination table. TO do so, I have to literally run 2 separate queries one that populates the temp table with the "organized" data in it's fields and then run another UPDATE that pushes the "organized" data from the temp table to the final destination table.

I've created a REPREX that exemplifies what I'm trying to achieve below:

/*
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

I'm trying to create a single queries that generates a derived query that does the group_concat part and then pushes that derived query result to the final destination table.

Any suggestions would be greatly appreciated.

Thank you for your time.

TB.

EDIT: Thank you for the replies I've got, but I'm trying to AVOID using the temp table.

I'm trying to AVOID creating a temp table.... I'm wondering how to do it in one go...

I was thinking something along the lines of:

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;

Unfortunately, the above doesn't work, I get the following error msg.

#1146 - Table 'fgcloud.dest' doesn't exist

Edit 2: I had a missing brackets in the above which caused a different error, I've fixed it but having issues with the table aliases. The table in question that should be updated is the "milestone_test_20221019" - it is declared as "dest", yet I it says it cannot find it, suggestions? The source table which I need to get the info and aggregate before updating "milestone_test_20221019" is the "item_shipped_ns" and I believe that "tmp" table is the derived/sub-query table alias...

CodePudding user response：

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

CodePudding user response：

Thanks for the assistance, after a few more test and tweaks I was able to achieve what I desired.

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;

Thank you for the people that commented and assisted me with their inputs.

Best regards, TB.