Doublet select/delete in mysql database

Question

Hi everybody ! Here is my question : I have a database (mysql) like this :

id0 id1 id2

And i would like to delete the lines who have the couple (id1,id2) in common to keep only one. Example : 1/1/1 2/1/2 3/1/2 <= Delete 4/2/1 5/2/3 6/2/3 <= Delete

I hope this is clear enough for you to help me :) Thanks

Answer 1

Assuming id0 is unique (thus the MIN(id0) for all equivalent tuples (id1, id2) is the only one to be kept):

DELETE
FROM tbl
WHERE id0 NOT IN (
    SELECT MIN(id0)
    FROM tbl
    GROUP BY id1, id2
)