How to Delete a Repiting(or)duplicate records from a Table?

Syedshakeer
Posted by Syedshakeer under Sql Server category on | Views : 2270
How to Delete a Repiting(or)duplicate records from a Table?

create table dupemp
(
name varchar(20),salary int,deptno int
)


insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)
insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)
insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)
insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)
insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)
insert into dupemp(name,salary,deptno) values('Khaja',3000,20)
insert into dupemp(name,salary,deptno) values('Khaja',3000,20)
insert into dupemp(name,salary,deptno) values('Naseeb',3000,20)


Query)
with CTE as(select row_number() over(partition by name order by name) row,name,salary,deptno from dupemp )
delete from CTE where row>1

5 row's will be affected

Select * from dupemp
OutPut:-

Name Salary deptno

inthiyaaz 2000 10
Khaja 3000 20
Naseeb 3000 20

Comments or Responses

Login to post response