Thursday, April 10, 2014

Delete duplicate rows using Common Table Expression

Here is another way to delete duplicate rows by means of acommon table expression(CTE). The CTE will lookup the business keys that have duplicate values. then the delete statement will join the table to the CTE by the business key field.

WITH a (CustomerId, OrderId, numrecs)
    AS (
        select CustomerId, OrderId, COUNT(*) as numrecs
        from dbo.ImportOrders
        group by CustomerId, OrderId having COUNT(*) > 1
        )
DELETE dbo.ImportOrders
FROM dbo.ImportOrders AS b
INNER JOIN a on a.CustomerId= b.CustomerId AND a.OrderId = b.OrderId
GO