MySQL: UPDATE with a JOIN and a GROUP_CONCAT

Question

Is this possible?

I have 2 tables, Customers and Orders. Now I want to fill a column in Customers with all order id's of that customer (comma separated).

I tried something like this, but it doesnt work:

UPDATE customers AS c
LEFT JOIN orders AS o ON o.customerid=c.customerid
SET c.orders = GROUP_CONCAT(DISTINCT o.orderid)

I get 'Invalid use of group function'.

PS. I know it's better to always dynamically get the GROUP_CONCAT values in a SELECT/JOIN, but I'm just wondering if I can fill this column in some way.

Answer 1

None of the given answers here were working for me, possibly because my case was more complicated (I needed more than one join), so I used Dennis' solution but split it into a temporary table:

CREATE TEMPORARY TABLE version_lookup
SELECT pduid, group_concat(version ORDER BY version SEPARATOR ',') AS 'versions'
     from items GROUP BY pduid;

UPDATE 
items i, version_lookup
SET i.versions = version_lookup.versions
WHERE version_lookup.pduid = i.pduid;

Answer 2

You will need to add an order by in the group_concat as shown in the example below

Note: group_concat(version ORDER BY version SEPARATOR ',')

UPDATE 
items i,
(SELECT pduid, group_concat(version ORDER BY version SEPARATOR ',') AS 'versions'
     from items GROUP BY pduid) AS version_lookup
SET i.versions = version_lookup.versions
WHERE version_lookup.pduid = i.pduid