How does SQL Delete duplicate data rows?

**************************************** * ******************* About: MSSQLServer2000 repeat sorting * Author: ZHUSHAN time: 2007-02-2811: 36 * test environment: SQLServer2000SP4 + Windows2000Sp1 ************************************* ***************

**************************************** * ******************* About: ms SQL Server 2000 repeat sorting * Author: Lushan time: * test environment: SQL Server 2000 SP4 + Windows 2000 Sp1 ******************************** ********************

**************************************** ******************
* About: ms SQL Server 2000RepeatedRecord sorting
* Author: Lushan time:
* Test environment: SQL Server 2000 SP4 + Windows 2000 Sp1
**************************************** ******************

There are two meaningsRepeatedRecord, one is completeRepeated, That is, all fields areRepeatedSecond, some key fields.RepeatedSuch as the Name field.RepeatedWhile other fields are not necessarilyRepeatedOr bothRepeatedCan be ignored.
1. For the first typeRepeated, Easy to solve, use
Select distinct * from tableName
You can get noneRepeatedThe result set of the record.
If this table requiresDeleteRepeated(RepeatedRecord keep 1), you can follow the methods belowDelete
Select distinct * into # Tmp from tableName
Drop table tableName
Select * into tableName from # Tmp
Drop table # Tmp
This occursRepeatedThe reason is that the table design is not weekly. You can add a unique index column.

2. This typeRepeatedThe problem is usually required to be retainedRepeatedThe first record in the record. The operation is as follows:
Assume thatRepeatedThe field Name and Address must be unique.
Select identity (int, 1, 1) as autoID, * into # Tmp from tableName
Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoID
Select * from # Tmp where autoID in (select autoID from # tmp2)
The Name and Address of the last select are obtained.RepeatedResult set of (but with one more autoID field, this column can be omitted in the select clause during actual writing)

3. Some key fieldsRepeatedAnd the record has an ID.

The first method can be used once.DeleteAllRepeated.. (Keep onlyRepeatedRecords with the smallest ID ).
Delete from table where id not in (select min (id) from table group by name)

Method 2DeleteRepeatedThe record with the largest ID in.
Delete from table where id in (select max (id) from table group by name having count (*)> 1)

4. SQL ProgramDelete

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