Problem with Delete Command with Subquery

[Cheryl Dixon]

I need help with the following query that I would like to use in MySQL to delete duplicate records, while retaining only the record with the minimum primary key (id) value. I have tried many syntaxes, but cannot get the follwing query to work:

DELETE from usr_iss_training
where (id)
not in
(select min(id)
from usr_iss_training where firstname = 'Zachary' and lastname = 'taylor'group by lastname having count(id)> 1);


I tried this first and received the following error:

MySQL said:

#1093 - You can't specify target table 'usr_iss_training' for update in FROM clause.


After doing some research, I found out thay MySQL will not let you reference the same table name in a delete clause using a subquery.

So, tried using aliases, like so:

DELETE from usr_iss_training As X where NOT EXISTS (select min(id) from usr_iss_training As y where x.id = y.id and y.firstname = 'Zachary' and y.lastname = 'taylor');

Then received the following error:

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where NOT EXISTS (select min(id) from usr_iss_training As y where x.id = y.id an' at line 1 .

Now I know that I have a count of three duplicate records for Zachary Taylor:

id firstname lastname
46 zachary taylor
45 zachary taylor
44 zachary taylor

What I want to do is to delete the two records with the higher id and retain only the record with the lowest id using the min () function.

Can anyone help with this query? I would appreciate it very much.

Thanks.