top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Deleting duplicate rows from a table.

+2 votes
473 views
Deleting duplicate rows from a table.
posted Jun 16, 2015 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote
 
Best answer

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-
(a) Using a temporary table.
(b) Without using a temporary table.

(a) Using a temporary or staging table
Let the table employee_test1 contain some duplicate data like:-

CREATE TABLE Employee_Test1
(
Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1: Create a temporary table from the main table as:-

select top 0* into employee_test1_temp from employee_test1

Step2 : Insert the result of the GROUP BY query into the temporary table as:-

insert into employee_test1_temp
select Emp_ID,Emp_name,Emp_Sal
from employee_test1
group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

truncate table employee_test1

Step4: Fill the original table with the rows of the temporary table as:-

insert into employee_test1
select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

select * from employee_test1

gives the result as:-

Emp_ID  Emp_name   Emp_Sal
1    Anees           1000
2     Rick     1200
3       John       1100
4 Stephen      1300
5        Maria       1400
6 Tim          1150

(b) Without using a temporary table

;with T as
(
 select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
        from employee_test1
)

delete
from T
where rank > 1

The result is as:-

Emp_ID  Emp_name   Emp_Sal
1    Anees           1000
2     Rick     1200
3       John       1100
4 Stephen      1300
5        Maria       1400
6 Tim          1150​
answer Jun 17, 2015 by Shivaranjini
+1 vote
answer Jun 16, 2015 by Amit Kumar Pandey
...