|
|
 |
| Author |
Message |
okidoky
|
 Posted: 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 Server272
|
| |
|
| |
|
Immy
|
| Posted: Wed Aug 02 07:50:43 CDT 2006 |
Top |
|
| |
|
Uir
|
| Posted: 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
|
| |
|
| |
|
| |
|