Delete duplicate rows

I have a table that looks like this

Table1

Id, Name

How can I write a q开发者_C百科uery that delete all rows with duplicate names but keep the one with the lower Id?

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
                )

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.

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