Introduction
In this article, I will explain how to delete duplicate records in a table by using SQL Query in a simple way.
Solution
• Create a table with the following structure
create table Emp_Data
(Eid int,
Ename varchar(50),
Sal float)
• Insert the values into the table
insert into Emp_Data values(1,'Ken J',15000)
insert into Emp_Data values(3,'Rob M',25000)
insert into Emp_Data values(4,'Diane L',20000)
insert into Emp_Data values(5,'Janice M',12000)
insert into Emp_Data values(6,'Kevin F',12500)
insert into Emp_Data values(1,'Ken J',15000)
insert into Emp_Data values(3,'Rob M',25000)
insert into Emp_Data values(5,'Janice M',12000)
insert into Emp_Data values(1,'Ken J',15000)
insert into Emp_Data values(2,'Terri Lee',10000)
• The data in this table as shown below
Eid
|
Ename
|
Sal
|
1
|
Ken J
|
15000
|
3
|
Rob M
|
25000
|
4
|
Diane L
|
20000
|
5
|
Janice M
|
12000
|
6
|
Kevin F
|
12500
|
1
|
Ken J
|
15000
|
3
|
Rob M
|
25000
|
5
|
Janice M
|
12000
|
1
|
Ken J
|
15000
|
2
|
Terri Lee
|
10000
|
Employee name “Ken J” repeated 3 times, “Rob M” repeated 2 times and “Janice M” repeated 2 times.
• If you want to delete all the rows if the selected columns repeated more than 1 then in the simple way by using the following method.
with
cte_Empdata as
(
select ROW_NUMBER() over(partition by Eid,Ename
order by eid) as rowno,* from Emp_Data
)
delete from cte_Empdata where
rowno>1
• After delete the duplicate records
Final Output is as follows
select * from Emp_Data
Eid
|
Ename
|
Sal
|
1
|
Ken J
|
15000
|
4
|
Diane L
|
20000
|
5
|
Janice M
|
12000
|
6
|
Kevin F
|
12500
|
3
|
Rob M
|
25000
|
2
|
Terri Lee
|
10000
|
Let me know what you think about this article.
