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