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 |