Interviews - C# Corner

Ravi Kumar
How can we delete Duplicate row in table?
By Ravi Kumar in SQL Server on Nov 14 2006
  • Lalit Raghuvanshi
    Feb, 2015 5

    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

    • 0
  • raj sekhar p
    Nov, 2006 22

    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

    • 0
  • Rajender Reddy
    Nov, 2006 22

    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

    • 0

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS