How to delete duplicate id's from a table where duplicate id's are allowed?
I have the following table student which allows duplicate id's as shown below:
Now in this table I need to delete all other duplicate record and leaving any one of the unique record of the id.
i.e for example if execute the delete statement 7 records should be deleted leaving 2 records one with id as '1' and another with id as '2'.
As shown in below diagram:-
[Final Expected output]
How can i write a single SQL query to get above result.
Below is the sample sql query I am trying which is throwing compile time error in sql editor as "unexpected student identifier".
DELETE FROM student as a WHERE a.sno not in(select b.sno from test.student as b group by b.id);
Kindly help me to figure out my mistake in query.
thanks in advance.
You can delete it by using your UNIQUE KEY: SNO. It is used to uniquely identify the record for delete action.
delete a.* from student a where a.sno not in ( select sno from ( select min(sno) as sno from student group by id) tab );
set sql_safe_updates = 0; DELETE FROM student WHERE sno NOT IN ( SELECT b.sno FROM (SELECT MIN(a.sno) AS sno FROM student a GROUP BY a.id) b); set sql_safe_updates = 1;
- The min() function indicates which row to keep from among the duplicates
- The nested subquery is to stop the the 'you can't specify target table ... for update in FROM clause' msg.
- The set_sql_safe_updates turns off error code 1175
delete from student where sno not in( select st.* from (SELECT sno FROM student group by id) st);
Your query is partially correct...but when you did group by id you have to again take those ids here I took in 'st'.so that all ids are in 'st' and the query will become like
delete from student where sno not in(1,6)