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?

stackoverflow.com/questions/45494/… , stackoverflow.com/questions/4471277/… — lc.
– lc., Commented Jul 17, 2012 at 14:50

Community · Accepted Answer · 2017-05-23 10:09:32Z

5

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

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered Jul 17, 2012 at 14:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL select refactor

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related