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:

Table with duplicate Id's.

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][2]

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.

[2]: https://i.stack.imgur.com/Z7tDc.pngenter image description hereenter image description here

3 answers

  • answered 2018-04-17 05:06 âńōŋŷXmoůŜ

    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
              );
    

  • answered 2018-04-17 05:06 jp2g

    Try this:

    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

  • answered 2018-04-17 05:06 Smita Ahinave

     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)