How can we delete Duplicate row in table?

Posted by Ravi Kumar in Database | SQL Server on Nov 14, 2006

Do you know the answer for this question? Post it below.

This Feature is Sponsored By

Posted by raj sekhar p on Nov 22, 2006

CREATE TABLE dbo.duplicateTest

(

[ID] [int] ,

[FirstName] [varchar](25),

[LastName] [varchar](25)

) ON [PRIMARY]

go
select * from duplicatetest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

WITH A(rowid,ID,FirstName,LastName) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS ROWID, * FROM duplicatetest

)

Delete A from A inner join A as B ON( A.rowid <> B.rowid and A.ID=A.ID

and A.FirstName=B.FirstName

and A.LastName=B.LastName

AND A.ROWID < B.ROWID

)

select * from duplicatetest

Posted by Rajender Reddy on Nov 22, 2006

SET ROWCOUNT 1

DELETE a1

FROM a1 a

WHERE (SELECT COUNT(*) FROM a1 b WHERE b.test1 = a.test1 AND b.test1 =

a.test1) > 1

WHILE @@rowcount > 0

DELETE a1

FROM a1 a

WHERE (SELECT COUNT(*) FROM a1 b WHERE b.test1 = a.test1 AND b.test1=

a.test1) > 1

SET ROWCOUNT 0

PREMIUM SPONSORS

TRENDING NOW

MOST LIKED QUESTIONS

In Focus