MySQL: UPDATE with a JOIN and a GROUP_CONCAT

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 bet开发者_如何转开发ter 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.

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

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;

Basically you should not use the GROUP_CONCAT function in this manner, that's not the right way of making your work done.

In this scenario you can use nested queries approach instead of trying with JOINs as I specified below, try this query, hopefully this should do your work correctly.

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

Try this query once and then let me know if you are facing any more issues.

Siva

You forget to tell the 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