0

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?

OMG Ponies
  • 314,254
  • 77
  • 507
  • 490
hd.
  • 16,676
  • 43
  • 110
  • 160
  • duplicate of http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause – littlegreen Nov 24 '10 at 13:38

1 Answers1

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.

BarsMonster
  • 6,395
  • 2
  • 33
  • 47