mysql group_concat as value for set statement

Question

I'm trying to update a field with a concated value of several rows.

so there is table a:

ID | single_value  
1  | hello
2  | something
1  | world
42 | another someting
1  | bye bye

....

and table b:

ID | concated_field
1  | ''  (i.e. empty )
2  | ''
3  | ''
4  | ''

as result, table b should be:

ID | concated_field
1  | hello, world, bye bye
2  | something
3  | ''
42 | another something

...

my query is:

    update data.table b, data.content a
    set b.concated_field= (
       select group_concat(single_value separator ', ') from data.table
    )
    where b.ID= a.ID;

but my result is something like:

ID | concated_field 
1  | hello, something, world,another something, bye bye
2  | ''
3  | ''
42 | ''

...

seems like the WHERE part is wrong but i don't get it. I am seriously looking for help!! :)

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, Jan 11 '17 at 15:16
Why do you want to save this `concated_field` in your database? — gen_Eric, Jan 11 '17 at 15:33

score 1 · Accepted Answer · answered Jan 11 '17 at 15:39

You need to use WHERE and GROUP BY clause in you subquery to achieve your expected result.

 update data.table b, data.content a
    set b.concated_field= (
       select group_concat(single_value separator ', ') 
       from data.table 
       where data.table.ID = b.ID
       group by data.table.ID
    )
    where b.ID= a.ID;

mysql group_concat as value for set statement

1 Answers1