SQLServer six methods for deleting duplicate rows

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.