Question 273553 in Mysql Question posted in
The official MySQL documentation can be found here.

Problem merging duplicate row into first row with mysql

can you give me an example request showing how I can combine two duplicate lines merge one?

id name no
123 aaa 1,2,3
124 aaa 4,5,6
125 aaa 7,8,9

i want this

id name no
123 aaa 1,2,3,4,5,6,7,8,9

I researched whether I could update with concat, but I couldn’t get any results.

Tags:

2

Answers


  1. 0

    You can use ORDER BY inside the GROUP_CONCAT :

    select min(id) as id, name, group_concat(no ORDER BY id ASC) as no
from mytable
group by name

    Demo here

    Login or Signup to reply.
  2. 0

    First do the update:

    UPDATE mytable
JOIN (
  SELECT MIN(id) AS id, GROUP_CONCAT(no) AS no
  FROM mytable
  GROUP BY name
) agg ON mytable.id = agg.id
SET mytable.no = agg.no;

    And then do the delete:

    DELETE m1
FROM mytable m1
JOIN mytable m2 ON m1.name = m2.name AND m1.id > m2.id;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search