SQL statement used by SQL Server to find and delete duplicate table records

There are many ways for SQLServer to search for and delete duplicate records in tables. Below I will list several commonly used SQL statements with good performance. If you need them, please refer to them.

There are many ways for SQL Server to find and delete duplicate records in tables. Below I will cite several commonly used SQL statements with good performance. If you need them, please refer to them.

In this example, we will use the following table, which has duplicate PK values. In this table, the primary key is two columns (col1 and col2 ). We cannot create a unique index or primary key constraint because the two rows have duplicate primary keys. This process demonstrates how to identify and delete duplicate primary keys.

The Code is as follows:
Create table t1 (col1 int, col2 int, col3 char (50 )) Insert into t1 values (1, 1, 'Data value one ') Insert into t1 values (1, 1, 'Data value one ') Insert into t1 values (1, 2, 'Data value two ')

The first step is to identify which rows have duplicate primary key values:

The Code is as follows:
SELECT col1, col2, count (*) FROM t1 Group by col1, col2 HAVING count (*)> 1

5. Delete duplicate rows from the original table. For example:

The Code is as follows:
DELETE t1 FROM t1, holdkey WHERE t1.col1 = holdkey. col1 AND t1.col2 = holdkey. col2

The above method is from the microsoft website. Below I have collected some solutions for the website. For details, refer.

Search for redundant duplicate records in the table (multiple fields)

The Code is as follows:
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 Repeated Records (multiple fields)

The Code is as follows:
Delete from vitae a where (. peopleId,. 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. SQL Server searches for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid