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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by BINU PRASAD.M » Delete Duplicates in SQL 2000, SQL 2005, SQL...

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

Rate Topic

Display Mode

Topic Options

Author

Message

BINU PRASAD.M

Posted Tuesday, May 29, 2012 12:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 12:36 PM
Points: 4, Visits: 36

-- 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
*/

Post #1307956

« Prev Topic | Next Topic »

Permissions