Mysql is throwing an error

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 ta开发者_如何转开发rget 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.

Why don't you just do this?

DELETE FROM mytable WHERE roll=1

Why don't you just write

DELETE FROM mytable WHERE roll=1

? The error occurs because MySql doesn't like it to fetch form a table in a subquery when the superior query modifies the same table.

Why not simply:

DELETE FROM mytable WHERE roll=1

Why are you using subquery? You can write it this way:

DELETE FROM mytable WHERE where roll=1;

This thread explains why you can't do this.

And in this example, you can just use:

DELETE FROM mytable WHERE roll=1