0

I would like to delete duplicate records from student table. I am using MySQL server version 5.7

create table student (
   sid int not null,
   sname varchar(50) not null,
   city varchar(50) not null
) ;

Delete duplicate records using self join is working fine

 delete t1 from student as t1
 inner join student as t2
 on t1.sid < t2.sid and t1.sname = t2.sname;

Delete duplicate records using sub query is not working

delete s1 from student
where sid > (select sid from (select min(sid) from student s2 
where s2.sname = sname) as t);

I tried instruction mentioned in the below link also

Delete duplicate rows using Sub-query

It is not working.

Ravi Kannan
  • 303
  • 1
  • 3
  • 11

2 Answers2

0

Stick with the join version. The problem with the subquery version is that MySQL does not recognize correlation clauses nested more than one level.

That makes it hard to construct the query. While possible, it is not really worth it. You know the right approach in MySQL. And the nested subqueries is really a hack to get around MySQL limitations that are not needed in other databases.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Your subquery method is not working because your second nested query 

select min(sid) from student s2 where s2.sname = sname

does not reference the table you want to delete from, i.e. the sname in s2.sname = sname references the same s2 table, so the subquery is the same as

select min(sid) from student s2 where s2.sname = s2.sname

If you want to stick to a subquery method, you should use a derived table like so:

DELETE FROM
   student
WHERE EXISTS ( 
   SELECT 1 FROM (SELECT * FROM student) AS s2
     WHERE s2.sname = student.sname AND student.sid > s2.sid)
Michael Kelso
  • 151
  • 1
  • 8