Skip to main content


Showing posts from August, 2020

How to delete duplicate records from a table by using a CTA in SQL Server

You can use a CTA (Common Table Expression)  to delete duplicate records from a SQL Server table by writing a statement like this; WITH Location_CTE AS ( SELECT [Id]       , [LocationQuery]       , [DetailsResponseJson]       , [DateCreated]       , [DateUpdated] ,          ROW_NUMBER () OVER (               PARTITION BY                      [LocationQuery]               ORDER BY                      [LocationQuery]          ) Row_Num   FROM [dbo] . [Location] ) DELETE FROM Location_CTE WHERE Row_Num > 1 (34902 rows affected) Completion time: 2020-08-05T11:43:14.4827453+01:00 This will return the number of rows affected and then you can check whether you still have duplicate records with a group by statement.     SELECT     [LocationQuery] ,               COUNT ( LocationQuery )      FROM      [dbo] . [Location]   GROUP BY      [LocationQuery]   ORDER BY      COUNT ( LocationQuery ) desc