0

I have a similar question to How to use GROUP BY to concatenate strings in MySQL? , however for this example for mytable table

id    string    aggr
1          A    NULL
1          B    NULL
2          F    NULL

The difference is I need to update the table to get this results:

id    string   aggr
1          A   A|B|
1          B   A|B|
5          C   C|E|C|
5          E   C|E|C|
5          C   C|E|C|
2          F   F|

As result the same id we have the same newcolumn values.

It is absolutely fine to add a delimiter |at the very end of the string. That way I can even faster count how many "items" are in the newcolumn without adding +1 because of the absense at the very end (just in between). Also I won't care about validation (if I have a pipe right before) when appending another part or two into aggr column.

Thank you.

Community
  • 1
  • 1
Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
  • Do you actually need to update a table, or just show the results in a view? You could use group_concat to combine multiple rows into 1 and actually use a count function to count the number of strings for a given ID. – xQbert Mar 06 '17 at 19:45
  • Yes, I need to update a table. And yes, I understand the table will have redundant data. – Haradzieniec Mar 06 '17 at 20:06

2 Answers2

2

You can try this query : 

UPDATE my_table t
SET aggr = (
    SELECT * FROM (
        SELECT CONCAT(GROUP_CONCAT(t2.string SEPARATOR '|'), '|')
        FROM my_table t2
        WHERE t2.id = t.id
   ) AS X
)
Guillaume Sainthillier
  • 1,655
  • 1
  • 9
  • 13
1

You could a group_concat joined on original table 

select a.id, a.string ,  b.aggr
from my_table a
inner join  (
  select id, group_concat(string SEPARATOR '|') as aggr
  from my_table 
  group by  id 
) b on a.id = b.id
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107