Delete duplicate rows

问题:

I have a table that looks like this

Table1

Id, Name

How can I write a query that delete all rows with duplicate names but keep the one with the lower Id?

回答1:

If you are using SQL Server 2005 or later:

With Dups As
    (
    Select Id, Name
        , Row_Number() Over ( Partition By Name Order By Id ) As Num
    From Table1
    )
Delete Table1
Where Id In (
            Select Id
            From Dups
            Where Num > 1
            )

If using SQL Server 2000 and prior

Delete Table1
Where Exists    (
                Select 1
                From Table1 As T1
                Where T1.Name = Table1.Name
                Having Min( T1.Id ) <> Table1.Id
                )

回答2:

The duplicates can be removed with a simple self join query. The below script would do the trick for you.

delete t2
from Table1 t1
join Table1 t2
   on t1.Name = t2.Name
where t1.Id < t2.Id

This logic can be used for cases where duplicates need to be removed. We should avoid "cursor" as much as possible as it blocks the table.

回答3:

Simply you can do this with using cursors the query might be like this

declare @id int declare @name nvarchar(30)

declare cr cursor for select id,name from idnametbl order by id

for update

open cr

fetch next from cr into @id,@name

while @@fetch_status=0

begin

delete from idnametbl where id> @id and name=@name

fetch next from cr into @id,@name

end

close cr

deallocate cr