Delete Duplicate Records Using CTE

Common Table Expression

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.

We need to create a table first. Copy the CityMaster table script and execute on the SQL Server under an appropriate database.
1. CREATE TABLE [CityMaster](
2. [Id] [int] PRIMARY KEY ,
3. [City] [varchar](50) NULL,
4. [State] [varchar](50) NULL
5. )
Once the table is created we need to now insert some records into the table that includes some duplicates.
1. insert into [CityMAster]
2. Select 1,'Aurangabad','BIHAR' union all
3. Select 2,'Aurangabad','MAHARASHTRA' union all
4. Select 3,'Bijapur','KARNATAKA' union all
5. Select 4,'Bijapur','CHHATTISGARH' union all
6. Select 5,'Bilaspur','CHHATTISGARH' union all
7. Select 6,'Bilaspur','HIMACHAL PRADESH' union all
8. Select 7,'Jamui','BIHAR' union all
9. Select 8,'Kullu','HIMACHAL PRADESH' union all
10. Select 9,'Pune','MAHARASHTRA' union all
11. Select 10,'Mumbai','MAHARASHTRA' union all
12. Select 11,'Kolhapur','MAHARASHTRA' union all
13. Select 12,'Nashik','MAHARASHTRA' union all
14. Select 13,'Mysore','KARNATAKA' union all
15. Select 14,'Raigarh','CHHATTISGARH'
16. Select 15,'Aurangabad','BIHAR' union all
17. Select 16,'Bilaspur','CHHATTISGARH' union all
18. Select 17,'Bijapur','KARNATAKA'
Execute the preceding script and check the data in the table by firing the following query.
1. 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.

5 Comments

Join the conversation! Your thoughts help the community grow.

hemanth gali9y ago
You are deleting rows in cte, but how does it deletes rows from original table
Karthik Muthu Karuppan11y ago
nice
- Nitin11y ago
  Thanks Karthik Muthu Karuppan
Khargesh Rajput11y ago
Nice
- Nitin11y ago
  Thanks :) Khargesh Rajput