Monday 8 June 2015

Simplest Method to delete duplicate records in a Table

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. 

2 comments:

  1. EXLLENT BROTHER I NEVER WATCH THIS TYPE OF BLOG AMAZING ANSWERS REALLY BUT I WANT
    ONE 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

    ReplyDelete
  2. Very nice article,Keep Updating more posts with us.
    Thank you..
    MSBI Course

    ReplyDelete