SQL Server searches for and deletes duplicate table records.

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:	Copy code
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:	Copy code
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:	Copy code
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:	Copy code
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:	Copy code
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