Thursday, July 18, 2013

Delete Duplicate Records – SQL

If there is an identity and unique column is available

Way 1:-

 WITH cte(ID, RankField)
AS (SELECT Id
, RankField = DENSE_RANK()
OVER (
PARTITION BY ID
ORDER BY newID())
FROM
Temp_ACTIVE)
DELETE FROM cte
WHERE RankField > 1

Way 2:-

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
--DELETE
Select *
FROM CTE
WHERE DuplicateCount > 1
GO

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

-----------------------------------

If thers is an unique column exists

-- Deleting Duplicate
DELETE
FROM
TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO

No comments:

Post a Comment