Ravi Kumar
How can we delete Duplicate row in table?
Posted by Ravi Kumar in SQL Server on Nov 14, 2006
  • 0
  • 2
  • 7866
Do you know the answer for this question? Post it below.
Guest
Posted by raj sekhar p on Nov 22, 2006
  • 0

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
  • 0



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


Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS

PRIVACY POLICY | TERMS & CONDITIONS | SITEMAP | CONTACT US | ABOUT US | REPORT ABUSE
2014© C# Corner. All contents are copyright of their authors.