How to Delete the Duplicate values in sql table?

 Posted by Saranya Boopathi on 8/27/2013 | Category: .NET Framework Interview questions | Views: 2677 | Points: 40
Answer:

create table:

Create table emp (Eno int ,EmpName varchar(10),Dept varchar(10))


Insert Values:


insert into emp values(1,'John','IT')
insert into emp values(1,'John','IT')
insert into emp values(2,'Maxx','CSE')
insert into emp values(3,'Cany','AUTO')
insert into emp values(2,'MAXX','CSE')
insert into emp values(1,'SONY','IT')


Select * from emp

Eno EmpName Dept
1 John IT
2 Maxx CSE
3 Cany AUTO
2 MAXX CSE
1 SONY IT
1 John IT


Find the duplicate values from the table:



WITH tempTable as(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)
select * from tempTable where RowNumber >1


Delete duplicate Rows with CTE


WITH tempTable as(

SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)
DELETE FROM tempTable where RowNumber >1


---After Deleting the duplicate values
select * from emp

Eno EmpName Dept
1 John IT
3 Cany AUTO
2 MAXX CSE
1 SONY IT


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Arul44ece on: 8/29/2013 | Points: 10
Hi Saran,

Please find the below query.

With CTE_Employee
AS
(
Select Employee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from #Employee
)

DELETE
FROM CTE_Employee
WHERE RowNumber > 1;

Login to post response

More Interview Questions by Saranya Boopathi