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