MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  no VARCHAR(50)
);
INSERT INTO mytable VALUES
    (1, 'aaa', '1,2,3'),
    (2, 'aaa', '4,5,6'),
    (3, 'bbb', '1,3'),
    (4, 'aaa', '7,8,9'),
    (5, 'bbb', '6');

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM mytable;


id
name
no


1
aaa
1,2,3

2
aaa
4,5,6

3
bbb
1,3

4
aaa
7,8,9

5
bbb
6


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;

Rows matched: 2  Changed: 2  Warnings: 0


SELECT * FROM mytable;


id
name
no


1
aaa
1,2,3,4,5,6,7,8,9

2
aaa
4,5,6

3
bbb
1,3,6

4
aaa
7,8,9

5
bbb
6


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


id
name
no


1
aaa
1,2,3,4,5,6,7,8,9

3
bbb
1,3,6