Monday, January 2, 2012

How to delete duplicate records from a Table (just copy paste it)

Note: it some times happen whenever a table 
do not have primary and it 
consist Duplicate Records
 
--Create Table
 
create table dupemp
 (
name varchar(20),salary int,deptno int
)

select * from dupemp

--Insert Records

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)

--Main Query to find and delete 
Duplicate Records

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

No comments:

Post a Comment