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.
EXLLENT BROTHER I NEVER WATCH THIS TYPE OF BLOG AMAZING ANSWERS REALLY BUT I WANT
ReplyDeleteONE QUESTION II HAVE I HAVE DATA IN EXCEL SHEET
I WANT IMPORT INTO SQL SERVER TABLE BUT CONDTION
I NEED ONLY FROM SHEET 2 ROWS AND 5 COLUMNS
BUT ACTUAL DATA IS 9 ROWS 18 COLUMNS
SPECIFIC DATA I NEED FROM SHEET TO SQL IS IT POSSIBLE IN INTERVIEW ASKED ME
Very nice article,Keep Updating more posts with us.
ReplyDeleteThank you..
MSBI Course
Aksaray
ReplyDeleteManisa
Osmaniye
Bingöl
Amasya
Erzurum
Rize
Karaman
Isparta