ERROR 1093 (HY000): You can’t specify target table ‘x’ for update in FROM clause

MySQL doesnt allow you to use a subquery in a delete cause with the same table name, such as:

DELETE a.*
FROM myTable a
WHERE id IN (
     SELECT id
     FROM myTable
     WHEREfoo = 'bar'
)

A workaround (create a derived table – then put your sub-query inside):

DELETE a.*
FROM myTable a
WHERE id IN (
     SELECT id FROM (
          SELECT id
          FROM myTable
          WHEREfoo = 'bar'
     ) as dTable
)

Leave a Reply

Your email address will not be published. Required fields are marked *