Summary of SQL statements for deleting duplicate data records in mysql

There are many ways to delete duplicate records. Here I summarize a variety of SQL statements for deleting restart records, such as deleting duplicate id data and searching for duplicate records, remove the smallest record, delete the repeat record, retain only one record and delete the redundant record in the table. The repeat record is determined based on a single field (eagleid), and only records with the smallest rowid are kept.

My most common method is

The Code is as follows:	Copy code
// Delete data with duplicate IDs, which is suitable for manual primary keys. Delete person as a from person as, ( Select *, min (id) from person group by id having count (1)> 1 ) As B Where a. id = B. id

// Find the duplicate and remove the smallest one

The Code is as follows:	Copy code
Delete tb_person as a from tb_person as, ( Select *, min (id) from tb_person group by name having count (1)> 1 ) As B Where a. name = B. name and a. id> B. id;

Now, let's summarize some more.

1. query the records to be deleted. A record is retained.

The Code is as follows:	Copy code
Select. id,. subject,. else er from test1 a left join (select c. subject, c. cycler, max (c. id) as bid from test1 c where status = 0 group by receiver, SUBJECT having count (1)> 1) B on. id <B. bid where. subject = B. subject and. worker ER = B. aggreger and. id <B. bid

2. Delete duplicate records. Only one record is retained. Note: subject and aggreger must be indexed. Otherwise, it will be slow.

The Code is as follows:	Copy code
Delete a from test1 a, (select c. subject, c. cycler, max (c. id) as bid from test1 c where status = 0 group by consumer er, SUBJECT having count (1)> 1) B where. subject = B. subject and. worker ER = B. aggreger and. id <B. bid;

3. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).

The Code is as follows:	Copy code
Select * from people Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

4. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

The Code is as follows:	Copy code
Delete from people Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

5. Delete redundant record (multiple fields) in the table, with only the records with the smallest rowid

The Code is as follows:	Copy code
Delete from vitae Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Let's give an example.

Example:

The Code is as follows:

Copy code

Drop table t_user; Create table t_user ( Id int (5) not null auto_increment, Username varchar (10 ), Age int (3 ), Primary key (id) ); Insert into t_user (username, age) values ('aaa', 20 ); Insert into t_user (username, age) values ('aaa', 20 ); Insert into t_user (username, age) values ('bbb ', 20 ); Insert into t_user (username, age) values ('bbb ', 20 ); Insert into t_user (username, age) values ('ccc ', 20 ); Insert into t_user (username, age) values ('ccc ', 20 ); Insert into t_user (username, age) values ('ddd ', 20 ); Insert into t_user (username, age) values ('ddd ', 20 ); Mysql> select * from t_user; + ---- + ---------- + ------ + | Id | username | age | + ---- + ---------- + ------ + | 1 | aaa | 20 | | 2 | aaa | 20 | | 3 | bbb | 20 | | 4 | bbb | 20 | | 5 | ccc | 20 | | 6 | ccc | 20 | | 7 | ddd | 20 | | 8 | ddd | 20 | + ---- + ---------- + ------ + Mysql> delete t_user from t_user, (select id from t_user group by username having count (*)> 1) as t2 where t_user.id = t2.id; Query OK, 4 rows affected (0.05 sec) Mysql> select * from t_user; + ---- + ---------- + ------ + | Id | username | age | + ---- + ---------- + ------ + | 2 | aaa | 20 | | 4 | bbb | 20 | | 6 | ccc | 20 | | 8 | ddd | 20 | + ---- + ---------- + ------ +