Thursday, December 3, 2015

Delete duplicate values by using CTE

How to Delete Duplicate values using CTE ?
CREATE TABLE [dbo].[emp]
(
[id] [int]  NULL,
[name] [varchar](20) NULL,
[city] [varchar] (20) NULL,
[dept] [varchar](10) NULL,
[salary] [money] NULL

)

insert into emp values(100,'Kishore','bangalore','IT',48000)
insert into emp values(101,'pavan','bangalore','HR',32000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

insert into emp values(105,'swetha','bangalore','Slaes', 26000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

Actual Table:

Required Result:


Query:
with e(edup)
As
(
             select id,
             ROW_NUMBER() over(PARTITION by id,name,city                      order by id ) as edup 
             from emp
)

delete from e where edup>1

Result:(3 row(s) affected)


We can also delete duplicates by using temporary table

  1. Selete distinct * into #emp123 from dbo.emp
  2. Truncate table dbo.emp
  3. Insert into Emp Select * from #emp123