Many developers face this problem, that they have
column with duplicate data, and want to delete duplicate records from SQL table
, but one should never delete duplicate record without observing that ,that
what is going to delete ,and prefer to
have latest record. So I am explain some steps to delete duplicate records from
table and assuming that and table has unique incremental id.
1.Table with
duplicate record:-
insert into student values('yashodhara')
insert into student values('New')
insert into student values('New')
insert into student values('Old')
insert into student values('Old')
insert into student values('Avarage')
insert into student values('Keep')
insert into student values('Keep')
insert into student values('After')
2. check data
select * from
student
Output is:
3.To find which record is duplicate
and how many times it is repeated
Select name,COUNT(*)as duplicate from student group by name having count(*)>1 order by count(*) desc
Out put is
4. Delate duplicate
records and keep lates record of every dulicate data
delete from student where
rollno not in(
select max(rollno) from student group by name
)
5 Check final data
select * from
student
Author: Er. Yashodhara Sharma
