MySQL select refactor

Question

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:

Why? (emphasis on the "why" so the Q is not inaccurately marked as "duplicate")
How to fix it?

http://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql — ethrbunny, Jul 17 '12 at 14:48
http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause , http://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition — lc., Jul 17 '12 at 14:50

score 5 · Accepted Answer · edited May 23 '17 at 10:09

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

MySQL select refactor

1 Answers1