SQL Server deletes six repeated rows

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.

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]

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.

Select distinct * into # temp from tablename

Delete tablename

Go

Insert tablename select * from # temp

Go

Drop table # temp

4. No ID

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

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.

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 ...)