Delete Duplicates in SQL 2000, SQL 2005, SQL 2008 or higher

• BINU PRASAD.M

  SSC Enthusiast

  Points: 120

  More actions

  May 29, 2012 at 12:25 pm

  #258793

  -- SQL 2005, SQL 2008 and Higher

  WITH XT(i, notes, RowNumber) AS

  (

  SELECT i, notes, ROW_NUMBER() OVER (PARTITION BY i, notes ORDER BY i, notes) AS RowNumber

  FROM dbo.temp

  )

  DELETE FROM XT WHERE RowNumber > 1;

  GO

  -- SQL 2000 Script

  DECLARE @DupCount INT

  SET @DupCount = 0

  SELECT @DupCount = COUNT(*)

  FROM dbo.temp

  GROUP BY i, notes

  HAVING COUNT(*) > 1

  WHILE @DupCount > 0

  BEGIN

  SET ROWCOUNT 1

  DELETE dbo.temp

  FROM dbo.temp t1

  JOIN (SELECT i, notes

  FROM dbo.temp

  GROUP BY i, notes

  HAVING COUNT(*) > 1

  ) t2

  ON t1.i = t2.i

  AND t1.notes = t2.notes

  SET ROWCOUNT 0

  SELECT @DupCount = COUNT(*)

  FROM (SELECT *

  FROM dbo.temp

  GROUP BY i, notes

  HAVING COUNT(*) > 1

  ) tp

  END

  GO

  SELECT * FROM dbo.temp

  /*

  -- Sample Create Table/Insert Scripts for above queries

  --DROP TABLE dbo.temp

  CREATE TABLE dbo.temp(i int, notes varchar(100))

  GO

  INSERT INTO dbo.temp VALUES (1, 'Notes1')

  INSERT INTO dbo.temp VALUES (1, 'Notes1')

  INSERT INTO dbo.temp VALUES (1, 'Notes1')

  INSERT INTO dbo.temp VALUES (2, 'Notes1')

  INSERT INTO dbo.temp VALUES (3, 'Notes1')

  INSERT INTO dbo.temp VALUES (1, 'Notes2')

  INSERT INTO dbo.temp VALUES (2, 'Notes2')

  INSERT INTO dbo.temp VALUES (2, 'Notes2')

  INSERT INTO dbo.temp VALUES (2, 'Notes3')

  INSERT INTO dbo.temp VALUES (4, 'Notes4')

  INSERT INTO dbo.temp VALUES (4, 'Notes4')

  GO

  */