A complete list of methods for querying and deleting duplicate records in MySQL

This article mainly introduces to you the methods of querying and deleting duplicate records in MySQL, and shares it for your reference and study. Let’s take a look at the detailed introduction below.

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 display one)

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

Note: The record with the largest ID is displayed here

2. Delete duplicate records

1. Delete all duplicate records (Use with caution)

Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

2 , keep one record (this should be what most people need^_^)

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

Note: Keep the record with the largest ID here

3. Example

1. Find redundant duplicate records in the table. Duplicate records are judged 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 judged based on a single field (peopleId), and only the record with the smallest rowid is left

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. Lookup the redundant duplicate records (multiple fields) in the lookup table

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

4. Delete the 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 the redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid. Record

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. The first is a completely duplicate record, that is, a record in which all fields are repeated, and the second is a partial duplicate record. Records with repeated key fields, such as the repeated Name field, but other fields may not be repeated or can be ignored.

1. For the first type of duplication, it is relatively easy to solve. Use

select distinct * from tableName

to get a result set without duplicate records.

If the table needs to delete duplicate records (retain 1 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

The reason for this duplication is poor table design. Add unique Index column can be solved.

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

Assume that the duplicate fields are Name and Address, and it is required to obtain the uniqueness of these two fields. The result set

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)

Summary

The above is the entire content of this article. I hope the content of this article can bring some help to everyone's study or work

Recommended learning: MySQL tutorial

The above is the detailed content of A complete list of methods for querying and deleting duplicate records in MySQL. For more information, please follow other related articles on the PHP Chinese website!