Wednesday, September 25

Deleting Duplicate Records from a Table using CTE

For this purpose we will create a table called  tblPopulation, 

------------Create Query-----------------------------------

Create table tblPopulation
(  In_Id int,
   Country varchar(255),
   [State]  varchar(255),
   City  varchar(255),
   [Population (in Millions)] int
);


-------------Insert Values into table--------------------------
Insert into tblPopulation  values
 (0,'India','Delhi','East Delhi',9),
(1,'India','Delhi','South Delhi',8),
(2,'India','Delhi','North Delhi',5),
(3,'India','Delhi','West Delhi',7),
(4,'India','Karnataka','Bangalore',9),
(5,'India','Karnataka','Belur',2),
(6,'India','Karnataka','Mysore',1),
(7,'India','Karnataka','Hubli',30),
(8,'India','Karnataka','Shimoga',20),
(9,'India','Karnataka','Bidar',11),
(10,'India','Karnataka','Mangalore',6);

--------------------------------------------------------------
Note: Insert same values two times so that duplicate records are formed in the table. Table will contain a total of 22 records.

To delete the duplicate records using CTE, Use the below query:

WITH Dup_Population (In_Id, Dup_Count)                           
AS                                                               
(                                                                
SELECT In_Id,                                                    
ROW_NUMBER() OVER(PARTITION BY In_Id ORDER BY In_Id) AS Dup_Count
FROM tblPopulation                                               
)                                                                
Delete FROM Dup_Population WHERE Dup_Count > 1 ;                 


Select * FROM tblPopulation;                                     

Output will produce Distinct 11 Records.