MySQL: UPDATE with JOIN and GROUP_CONCAT

Question

Is it possible?

I have 2 tables, customers and orders. Now I want to fill in the column in the Clients with all the order identifier of this client (separated by a comma).

I tried something like this, but it does not 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 that it is always better to dynamically get GROUP_CONCAT values ​​in SELECT / JOIN, but I'm just wondering if I can somehow fill this column.

Answer 1

You need to add the order to group_concat, as shown in the example below

: group_concat ( ORDER BY 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

Answer 2

, GROUP_CONCAT , .

, JOINs, , , , .

UPDATE customers AS c
SET c.orders = 
(SELECT GROUP_CONCAT(DISTINCT o.orderid) 
 FROM orders AS o 
 WHERE o.customerid = c.customerid 
 GROUP BY o.customerid);

, , .

Answer 3

None of the answers given here worked for me, perhaps because my case was more complicated (I needed more than one connection), so I used Denis's 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 4

Forget to specify a GROUP BY clause.

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