mysql - concat fields that have a duplicate key in common and remove those dupes leaving one -

this bugging me last week now, , basic knowledge can't to solution brings need be. i've searched on updateing, group_concat, concat, joins, cant result need.

the problem follows:

we have 2 columns work 1 result need:

the db we're working on:

+------------------------------------+-------------+   | id                                 | value       |   +------------------------------------+-------------+   |  d44172cb5c086c19a4c0286270916bc52 |           |   |  d44172cb5c086c19a4c0286270916bc52 | c           |   |  d44172cb5c086c19a4c0286270916bc52 | b           |   |  d87d93570cbc9a3edc18601e0aff6e261 | d           |   |  d87d93570cbc9a3edc18601e0aff6e261 |           | |  d87d93570cbc9a3edc18601e0aff6e261 | f           | +------------------------------------+-------------+  

what expect get:

 +-------------------------------------+-------------+    | id                                  | value       |    +-------------------------------------+-------------+    |  d44172cb5c086c19a4c0286270916bc52  | a|c|b       |    |  d87d93570cbc9a3edc18601e0aff6e261  | d|a|f       |    +-------------------------------------+-------------+  

we're using select command works , giving data expected in temporary(since we're selecting) column named valuenew:

select * group_concat(value separator '|') valuenew db.table group id ; 

how make (update or delete) query deletes duplicates, after concatenated needed values? because, using group_concat returned number of rows less actual rows present.

we need combine concatenated values in later stage other columns rows need equal returned rows.

solution

create table if not exists `temp`  select *, group_concat(`value` separator '|') `values` `db`.`table`  group `id`;  truncate table `db`.`table`;  update `temp` set value = values; alter table `temp` drop column values;  insert `db`.`table`([all column titles here]) select *  `db`.`temp` ;  drop table `db`.`temp`; 

if must, direct way of doing it.

create table `db`.`temp` select `id`, group_concat(`value` separator '|') `value` `db`.`table`  group `id`;  truncate table db.table;  insert `db`.`table`(`id`, `value`) select `id`, `value`  `db`.`temp` ;  drop table `db`.`temp` 

of course, if there other fields, you'll have account them appropriately prevent data loss. you make temp table true temporary table, if lose connection between truncate , re-insert you'd lose data.

edit: make sure value field can hold longest group_concat() result.