Delete Duplicate Records From Table Using CTE

Introduction 

CTE is

CTE is common table expression. It is a temporary named result set derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTE can be used for writing complex recursive queries. It is much more popular than temporary tables.

Syntax

WITH [CTEName]
AS
(
SELECT column1, column2, column3 FROM [TableName] WHERE [condition]
)
SELECT column1, column2, column3 FROM [CTEName]

Sample Demo

In the following example we will see how to delete duplicate records from a table using CTE.

Use the following procedure.

1. We need to create a table first. Copy the CityMaster table script and execute on the SQL Server under an appropriate database.
   

   CREATE TABLE [CityMaster](
   [Id] [int] PRIMARY KEY ,
   [City] [varchar](50) NULL,
   [State] [varchar](50) NULL
   )

2. Once the table is created we need to now insert some records into the table that includes some duplicates.
   

   insert into [CityMAster]
   Select 1,'Aurangabad','BIHAR' union all
   Select 2,'Aurangabad','MAHARASHTRA' union all
   Select 3,'Bijapur','KARNATAKA' union all
   Select 4,'Bijapur','CHHATTISGARH' union all
   Select 5,'Bilaspur','CHHATTISGARH' union all
   Select 6,'Bilaspur','HIMACHAL PRADESH' union all
   Select 7,'Jamui','BIHAR' union all
   Select 8,'Kullu','HIMACHAL PRADESH' union all
   Select 9,'Pune','MAHARASHTRA' union all
   Select 10,'Mumbai','MAHARASHTRA' union all
   Select 11,'Kolhapur','MAHARASHTRA' union all
   Select 12,'Nashik','MAHARASHTRA' union all
   Select 13,'Mysore','KARNATAKA' union all
   Select 14,'Raigarh','CHHATTISGARH'
   Select 15,'Aurangabad','BIHAR' union all
   Select 16,'Bilaspur','CHHATTISGARH' union all
   Select 17,'Bijapur','KARNATAKA'

3. Execute the preceding script and check the data in the table by firing the following query.
   

   select * from [CityMaster] order by city,[state]

              

As we can see, there are duplicate records in the table. So let us proceed to delete the duplicates from the table.

We will use the row_number() function with CTE for numbering duplicate city records by state. CTE will provide us a temporary result set using which we can delete duplicate records easily from the actual table using a single query. Write the following query.

With CTE as
(
Select Id,city,[state],row_number() over (partition by City,[state] order by City) as CityNumber from [CityMaster]
)
Select * from CTE order by city,[state]

In the preceding script we have taken a citynumber column that contains the count of the cities by state. For repeating the city in the citynumber column has a value greater than 1.

Execute the preceding script and check the output.

CityNumbers with value greater than 1 are being repeated for the corresponding state. So let us check by verifying this via query. We will select all the cities with citynumber greater than 1.

We got the duplicates in the table now. Now we will delete the repeating data.

Deletion of duplicate records with CTE

Select/Insert/Update/Delete statements can be used with CTE.

For deleting duplicate records execute the following query. This query will delete all the duplicates from the table.

With CTE as
(
Select Id,city,[state],row_number() over (partition by City,[state] order by City) as CityNumber from [CityMaster]
)
delete from CTE where CityNumber >1

Run the following query to check the table data after deleting duplicate rows.

select * from [CityMaster] order by city,[state]
** select from citymaster **

So in this way we can delete the duplicates using CTE in SQL.