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.

score 11 · Answer 1 · answered Aug 26 '12 at 04:33

11

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

answered Aug 26 '12 at 04:33

Dennis

111
1
3

You will need to place the subquery within another subquery otherwise you will get the **You can't specify target table 'tablename' for update in FROM clause** - [see this](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12508381#12508381) for further details. – Fluffeh Sep 27 '12 at 11:02

score 5 · Answer 2 · answered Nov 16 '12 at 14:42

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;

score 3 · Answer 3 · answered Jun 18 '12 at 12:49

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

score -1 · Answer 4 · answered May 23 '11 at 16:10

-1

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

answered May 23 '11 at 16:10

Pentium10

204,586
122
423
502

2

I don't think that's the problem. You can use GROUP_CONCAT without specifying GROUP BY. Plus it's still giving an error when I try this. – Dylan May 23 '11 at 16:15

MySQL: UPDATE with a JOIN and a GROUP_CONCAT

4 Answers4