0

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

jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
srik123
  • 3
  • 2
  • https://stackoverflow.com/a/5016434/2893413 may be useful to you – Sadikhasan Apr 17 '18 at 05:14
  • Sure there are ways to delete duplicates, the answers show plenty, but in your case it does not look like deletion is what you want to do, because then you will be deleting data that looks perfectly valid. It looks like all you really need to do is delete the bogus `id` column. – Mike Nakis Apr 17 '18 at 12:31

3 Answers3

0

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
          );
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
0

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
jp2g
  • 682
  • 4
  • 8
0
 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)
Smita Ahinave
  • 1,901
  • 7
  • 23
  • 42