How to delete duplicate records of single table in MySQL
This article to share with you is about MySQL how to delete single table duplicate records, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.
1. Create table test001
Click here to fold or open
CREATE TABLE `test001` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2. Random write data guide table test001
insert into test001 (name) values('A');
insert into test001 (name) values('B');
insert into test001 (name) values('C');
insert into test001 (name) values('d');
3. Query the whole table data
Click here to fold or open
select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | A |
| 5 | A |
| 6 | A |
| 7 | A |
| 8 | A |
| 9 | B |
| 10 | B |
| 11 | B |
| 12 | B |
| 13 | B |
| 14 | B |
| 15 | C |
| 16 | C |
| 17 | C |
| 18 | C |
| 19 | d |
| 20 | d |
| 21 | d |
| 22 | d |
| 23 | d |
+----+------+
23 rows in set (0.00 sec)
4. Execute SQL to delete duplicate records and keep only the records with the smallest id
DELETE FROM Test001 WHERE id NOT IN (
SELECT minid FROM
(SELECT min(id) AS minidFROM Test001
GROUP BYname) b
);
Click here to fold or open
>DELETE
-> FROM
-> Test001
-> WHERE
-> id NOT IN (
-> SELECT
-> minid
-> FROM
-> (
-> SELECT
-> min(id) AS minid
-> FROM
-> Test001
-> GROUP BY
-> name
-> ) b
-> );
Query OK, 19 rows affected (0.00 sec)
(root@localhost:mysql.sock) [test]>select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 9 | B |
| 15 | C |
| 19 | d |
+----+------+
4 rows in set (0.00 sec)
5. After execution, duplicate records are deleted.
Click here to fold or open
(root@localhost:mysql.sock) [test]>select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 9 | B |
| 15 | C |
| 19 | d |
+----+------+
4 rows in set (0.00 sec)
The above is how to delete duplicate records in a single table in MySQL. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
