DELETE FROM mytable WHERE id IN (SELECT id FROM mytable where roll=1)
I have a table mytable
. My above query is throwing an error.
You can't specify target table 'mytable' for update in FROM clause
From the MySQL documentation:
Currently, you cannot delete from a table and select from the same table in a subquery.
Fortunately, you don't need the subquery. Just do:
DELETE FROM mytable WHERE roll=1
It's much shorter and clearer to boot.