A complete guide on how to query and delete duplicate records in MySQL

Preface

This article mainly introduces the methods of querying and deleting duplicate records in MySQL. It is shared for your reference and learning. Let's take a look at the detailed introduction:

Find all records with duplicate titles:

select title,count(*) as count from user_table group by title having count>1;

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

1. Find duplicate records

1. Find all duplicate records

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

2. Filter duplicate records (only one is displayed)

Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)

Note: This shows the record with the largest ID

2. Delete duplicate records

1. Delete all duplicate records ( use with caution )

Delete table Where repeated fields In (Select repeated fields From table Group By repeated fields Having Count(*)>1)

2. Keep one (this should be what most people need^_^)

Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)

Note: The record with the largest ID is retained here

3. Examples

1. Find redundant duplicate records in the table. Duplicate records are determined based on a single field (peopleId)

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2. Delete redundant duplicate records in the table. Duplicate records are determined based on a single field (peopleId). Only the record with the smallest rowid is retained.

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)

3. Find redundant duplicate records in the table (multiple fields)

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

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

delete from vitae a 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)

5. Find redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid

select * from vitae a 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)

4. Supplement

There are more than two duplicate records. One is a completely duplicate record, that is, a record with all fields duplicated. The other is a record with some key fields duplicated, such as the Name field is duplicated, while other fields may not be duplicated or may be duplicated and can be ignored.

1. For the first type of repetition, it is easier to solve. Use

select distinct * from tableName

You can get a result set without duplicate records.

If the table needs to delete duplicate records (retain only one duplicate record), you can delete it as follows:

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

This duplication occurs due to poor table design and can be resolved by adding a unique index column.

2. This type of duplicate problem usually requires retaining the first record of the duplicate records. The operation method is as follows

Assume that there are repeated fields named Name and Address, and you want to get a unique result set for these two fields.

select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.