CODEWITHSUNDEEP
mysql
Robottinosino
Robottinosino

Reputation: 10882

MySQL select refactor

On MySQL, this does not run:

delete from robottinosino where date = (select max(date) from robottinosino);

Error message:

ERROR 1093 (HY000): You can't specify target table 'robottinosino' for update in FROM clause

Question:

Upvotes: 0

Views: 79

Answers (1)

Marc B
Marc B

Reputation: 360692

MySQL doesn't allow you to delete from a table that you're also selecting from. It's a race condition in many cases - you could be deleting records from the table before the select part of the query has had a chance to retrieve them.

In your case, the query should be allowed, because there isn't any danger of this race, but MySQL isn't smart enough to figure that out.

There're a workarounds here: MySQL Error 1093 - Can't specify target table for update in FROM clause

Upvotes: 5

Related Questions