MySQL delete common rows but limit rows

Question

I want to limit the number of rows to delete during a loop. Below are the tables. Please note this is sample and both tables have 20+ columns and millions of rows :

Table A1: 
ID  Name
1   Tony 
2   Andy
3   Nate 

A2
ID  Name
1   Tony 
2   Andy 

I want to delete only 1 row at a time, and will use a loop to re-run the same sql. I tried :

delete from a1 where id  in (select id from a1 inner join a2 on a1.id = a2.id limit 1); 

says :

you cannot specify target table 'a1' for update in FROM clause.

Is there a way I can do this in mysql?

Answer 1

I have no idea why you would use a loop. However I suspect you want a correlated subquery:

delete from a1
    where a1.id in (select a2.id from a2 where a1.id = a2.id limit 1); 

Note: The use of limit without order by looks really dangerous. This will delete an arbitrary row for each a1.id.