skip to Main Content

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.

2

Answers


  1. Chosen as BEST ANSWER

    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;
    

  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search