SQL statement to query duplicate records and delete Repeated Records

Search for records with all repeated titles:

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

1. Search for duplicate records
1. Search for all Repeated Records

Select * From table Where repeated field In (Select repeated field From Table Group By repeated field Having Count (*)> 1)

2. Filter duplicate records (only one record is displayed)

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

Note: The maximum ID record is displayed here.

Ii. Delete duplicate records
1. Delete All Repeated Records (use with caution)

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

2. Keep one record (this should be the token _^ required by most people)

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

Note: The maximum ID record is retained here.

Iii. Example

1. Search for 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 unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

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. Search for 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 record (multiple fields) in the table, leaving only the records 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. Search for redundant duplicate records (multiple fields) in the table, excluding records 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)

Iv. Supplement
There are more than two duplicate records. One is a completely repeated record, that is, record with all fields already exists. The other is record with duplicate key fields, such as duplicate Name fields, other fields are not necessarily repeated or can be ignored.

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

select distinct * from tableName

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.

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