Query and delete duplicate records in SQL

There are often repeated records in the database. sometimes I want to query duplicate records and display them, and sometimes I want to delete them. next I will introduce how to delete and query duplicate records.

There are often repeated records in the database. sometimes I want to query duplicate records and display them, and sometimes I want to delete them. next I will introduce how to delete and query duplicate records.

1. Search for all repeated records

The code is as follows:
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)

The code is as follows:
Select * From HZT Where ID In (Select Max (ID) From HZT Group By Title)

Note: The maximum ID record is displayed here.

Deleting duplicate rows on SQL Server is one of the most common operations. The following describes six methods that can be used to delete duplicate rows on SQL Server for your reference.

1. if an ID field exists, it is a unique field.

The code is as follows:
Delect table tableName where id not in (select max (id) from table group by col1, col2, col3 ...)

The field followed by the group by clause is the condition for you to judge repetition. for example, if only col1 is used, if the content of col1 is the same, the record is the same.

2. if you want to determine all fields, you can also [check whether the specified fields in the table are the same]

The code is as follows:
Select * into # temp from tablename group by id1, id2 ,.... Delete tablename Insert into table select * from # temp Drop table # temp

3. first, repeat and obtain N * 1 pieces of data and insert it into the temporary table. [check whether all fields in the table are the same.] then, delete the data in the original table, insert the data in the temporary table to the original table, and delete the temporary table.

The code is as follows:
Select distinct * into # temp from tablename Delete tablename Go Insert tablename select * from # temp Go Drop table # temp

4. no ID

The code is as follows:
Select identity (int, 1, 1) as id, * into # temp from tabel Delect # where id not in ( Select max (id) from # group by col1, col2, col3 ...) Delect table Inset into table (...) Select... from # temp

5. col1 + ',' + col2 + ','... col5 join primary key

The code is as follows:
Select * from table where col1 + ',' + col2 + ','... col5 in ( Select max (col1 + ',' + col2 + ','... col5) from table Where having count (*)> 1 Group by col1, col2, col3, col4 )

The field followed by the group by clause is the condition for you to judge repetition. for example, if only col1 is used, if the content of col1 is the same, the record is the same.

6.

The code is as follows:
Select identity (int, 1, 1) as id, * into # temp from tabel Select * from # temp where id in ( Select max (id) from # emp where having count (*)> 1 group by col1, col2, col3 ...)

Supplement other methods

1. Delete all duplicate records (use with caution)

The code is as follows:
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)

The code is as follows:
Delete HZT Where ID Not In (Select Max (ID) From HZT Group By Title)

Note: The maximum ID record is retained here.

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

The code is as follows:
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

The code is as follows:
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)

The code is as follows:
Select * from vitae 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

The code is as follows:
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)

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

The code is as follows:
Select * 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)