SQL statement used to delete duplicate records in SQLServer

This article will introduce to you in detail the summary of SQL statement methods for deleting duplicate records in SQLServer. In general, we will delete the records in two cases, one is a completely repeated record, where all fields are repeated, and the other is a record with duplicate key fields. other fields are not repeated or repeated, but can be ignored.

This article will give you a detailed summary of the SQL statement methods used to delete duplicate records in SQL Server. In general, we will delete duplicate records in two cases, one is a completely repeated record, where all fields are repeated, and the other is a record with duplicate key fields. other fields are not repeated or repeated, but can be ignored.

Method:

The code is as follows:
Distinct * into # tmp from tablename drop table tablename select * into tablename from # tmp Drop table # tmp

We often encounter the need to delete repeated records in SQL Server. here there are some commonly used SQL statements to delete repeated records,

The most commonly used T-SQL statement:

The code is as follows:
Delete from [dbo]. [myTable] WHERE primary key NOT IN (Select max (primary key) FROM [dbo]. [myTable] group by column 1, column 2, column 3) After SQL Server 2005, use CTE: WITH tmpOrderdTable AS ( SELECT GroupID = ROW_NUMBER () OVER (partition by column 1, column 2, column 3 order by primary key) FROM [Dbo]. [myTable] ) Delete from tmpOrderdTable WHERE GroupID> 1

To improve efficiency, you can first enable the single-user access mode, and then restore the multi-user access mode after deletion:

# Enable single-user access mode

The code is as follows:
USE [master] Alter database [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE # Enable multi-user access mode USE [master] Alter database [myDB] SET MULTI_USER with rollback IMMEDIATERelated Posts

Delete an SQL Stored Procedure

The code is as follows:
Declare @ max integer, @ id integer Declare cur_rows cursor local for select main field, count (*) from table name main field having count (*)> 1 Open cur_rows Fetch cur_rows into @ id, @ max While @ fetch_status = 0 Begin Select @ max = @ max-1 Set rowcount @ max Delete from table name where primary field = @ id Fetch cur_rows into @ id, @ max End Close cur_rows Set rowcount 0

A: retain the row with the largest id and delete other rows.
Method 1

The code is as follows:
Delete [user] from [user] t Inner join (select name, max (id) as id from [user] group by name) On t. name = a. name and t. id <> a. id

B: Keep the row with the smallest id and delete other rows.

Method 1

The code is as follows:
Delete [user] from [user] t Inner join (select name, min (id) as id from [user] group by name) On t. name = a. name and t. id <> a. id