Ravi Kumar
How can we delete Duplicate row in table?
Posted by Ravi Kumar in SQL Server on Nov 14, 2006
  • 0
  • 3
  • 8414
Do you know the answer for this question? Post it below.
Guest
Posted by Lalit Raghuvanshi on Feb 05, 2015
  • 0

Please refer the article Remove duplicate records/data from Sql Server database table http://www.webcodeexpert.com/2013/11/how-to-remove-duplicate-recordsdata.html

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


Download Free eBooks

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS