Geek asks and answers

Delete duplicate rows

I have a table that looks like this:

Table 1

ID, name

How can I write a query that deletes all rows with duplicate names but saves one with a lower id?

+3
source share
3 answers

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 you are using SQL Server 2000 or earlier

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

Duplicates can be removed with a simple join request. The below script will 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 when duplicates need to be removed. We should avoid the "cursor" as much as possible, since it locks the table.

+2
source

:

@id int declare @name nvarchar (30)

cr id, idnametbl id

cr

cr @id, @name

while @@fetch_status = 0

remove from idnametbl, where id> @id and name = @name

select the next from cr to @id, @name

end

close cr

deallocate cr

0
source

More articles:


All Articles