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
  1. WITH [CTEName]   
  2. AS  
  3. (  
  4. SELECT column1, column2, column3 FROM [TableName] WHERE [condition]  
  5. )  
  6. 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.
    1. CREATE TABLE [CityMaster](  
    2. [Id] [intPRIMARY KEY ,  
    3. [City] [varchar](50) NULL,  
    4. [State] [varchar](50) NULL  
    5. )  
  2. 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'   
  3. 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.
  1. With CTE as   
  2. (  
  3. Select Id,city,[state],row_number() over (partition by City,[state] order by City) as CityNumber from [CityMaster]  
  4. )  
  5. 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.
 
Delete Duplicate Records Using CTE 
 
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.
 
Delete Duplicate Records Using CTE
 
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.
  1. With CTE as   
  2. (  
  3. Select Id,city,[state],row_number() over (partition by City,[state] order by City) as CityNumber from [CityMaster]  
  4. )  
  5. delete from CTE where CityNumber >1   
Delete Duplicate Records Using CTE 
 
Run the following query to check the table data after deleting duplicate rows.
  1. select * from [CityMaster] order by city,[state]   
  2. ** select from citymaster **  
So in this way we can delete the duplicates using CTE in SQL.