Delete Dupes using CTE

for my own reference:
delete dupe records, keeping one entry for each

;WITH mycte (PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,myrank)
AS
(
SELECT
PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,
RANK() OVER (PARTITION BY PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId
ORDER BY PKFACTMarketDataId) AS myrank
FROM DCRMCube.FACTMarketData
)

DELETE FROM mycte
WHERE MYRANK >1

Tags: ,

Friday, March 2nd, 2012 SQL Server

No comments yet.

Leave a comment

You must be logged in to post a comment.