delete from nested select result problem

Question

i have written this query:

delete from `test1` where id in (
SELECT id
FROM `test1`
GROUP BY m, n
HAVING count( id ) >1)

but mysql gives this error:

'You can't specify target table 'test1' for update in FROM clause '

why? how can i solve it?

Answer 1

You are updating table while select is in progress... THat' not a piece of cake :-) I would suggest to select into tmp table first, and do delete in second query.