SQLServer deleting duplicate rows is one of the most common operations. The following describes six methods for deleting duplicate rows in SQLServer for your reference. 1. If there is an ID field, it is a unique field delecttabletableNamewhereidnotin (selectmax (id) fromtablegroupbycol1, col2, col3. ..) gro
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 there is an ID field, it is a unique field delecttable tableName whereidnotin (selectmax (id) fromtablegroupbycol1, col2, col3. ..) gro
To delete duplicate rows, SQL Server is one of the most common operations in the United States space. The following describes how to delete duplicate rows by SQL Server in different situations.
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 the table, obtain N * 1 data records, insert them to the temporary table, [check whether all fields in the table are the same], and then delete the data in the original table. The Hong Kong server, 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
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 (
, Hong Kong server rent