Method:
The code is as follows: |
Copy code |
Select 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: |
Copy code |
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: |
Copy code |
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: |
Copy code |
Declare @ max integer, @ id integer Declare cur_rows cursor local for select main field, count (*) from table name group by 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: |
Copy code |
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: |
Copy code |
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
|