Reputation: 2418
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 target table 'mytable' for update in FROM clause
Upvotes: 0
Views: 53
Answers (6)
Reputation: 597096
This thread explains why you can't do this.
And in this example, you can just use:
DELETE FROM mytable WHERE roll=1
Upvotes: 0
Reputation: 1194
Why are you using subquery? You can write it this way:
DELETE FROM mytable WHERE where roll=1;
Upvotes: 0
Reputation: 5462
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.
Upvotes: 0
Reputation: 24140
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.
Upvotes: 3