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?

Btw, I think you've been here long enough to know how Stack rolls. So many questions, and none accepted. Your interaction on solutions given also is left to be questionable. — Funk Forty Niner, Dec 28 '16 at 21:14
See the linked question for how to solve the "cannot specify target table for update" problem. Use that solution and add `LIMIT` to it. — Barmar, Dec 28 '16 at 21:14
I don't think the linked duplicate is really a duplicate. I'm pretty sure the right solution is to convert the subquery to a correlated subquery, which is not covered by that answer. — Gordon Linoff, Dec 28 '16 at 21:16
@Fred-ii- got it. thanks for letting me know of something i overlooked. — Arun Srini, Dec 29 '16 at 16:10
@ArunSrini You're welcome and thanks. This makes it easy for people to know that the question was solved. In not doing so, others may think that the question(s) is/are still open/unsolved and may be tempted to post (more) answers. — Funk Forty Niner, Dec 29 '16 at 16:12

score 0 · Accepted Answer · answered Dec 28 '16 at 21:14

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.

MySQL delete common rows but limit rows

1 Answers1