Deleting duplicates  
Author Message
okidoky





PostPosted: Wed Aug 02 07:50:43 CDT 2006 Top

SQL Server Developer >> Deleting duplicates

How do i delete duplicate rows in a table?
--
Dare To Dream, Care To Achieve

SQL Server17  
 
 
Immy





PostPosted: Wed Aug 02 07:50:43 CDT 2006 Top

SQL Server Developer >> Deleting duplicates http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Enjoy



> How do i delete duplicate rows in a table?
> --
> Dare To Dream, Care To Achieve


 
 
Uir





PostPosted: Wed Aug 02 07:53:55 CDT 2006 Top

SQL Server Developer >> Deleting duplicates Hi
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)

INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)

PRINT 'Table'
SELECT * FROM #Demo

PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)

PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)

DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)

PRINT 'Cleaned-up Table'
SELECT * FROM #Demo

DROP TABLE #Demo



> How do i delete duplicate rows in a table?
> --
> Dare To Dream, Care To Achieve