Question:
The MySQL documentation says:
you cannot modify a table and select from the same table in a subquery
you cannot change a table by selecting data from it in a subquery
This applies to DELETE , UPDATE and INSERT . The solution is usually to replace the subquery with something that gives the same result, such as a JOIN , or several.
I know SQL Server doesn't have this restriction. Anyone who knows about Postgres and Oracle says there in the comments. My question is: why does MySQL have this restriction? What do they do (or fail to do) so that this is not possible?
Answer:
The only data closest to an explanation provided in the documentation itself is that the change works if the record comes from a "derived" table, as in the example below, and that this is due to the fact that the derived data is materialized in a temporary table :
UPDATE t … WHERE col = (SELECT * FROM (SELECT … FROM t…) AS dt …);
Extrapolating from this information, this behavior may be the result of the need to avoid conflicting locks. An UPDATE from a SELECT becomes impossible because the engine does not allow obtaining an exclusive lock for a record that is already read-locked in another transaction.
Thinking this way, the example above works because, when faced with a "sub-subselect", the optimizer decides to materialize the query in a temporary table: the record that receives a read lock is a copy, elsewhere, of the one that will receive an exclusive lock to UPDATE, avoiding conflict.
That said, I recognize that this framework I created doesn't make much sense =/. InnoDB's documentation is very detailed to emphasize that the engine supports tuple multiversioning and the four lock isolation levels, there's no reason not to do this type of operation normally as in PostgreSQL, Oracle and similar. I can only believe that this is a remnant of MyISAM implementation since pre-5.5.5 versions, since the only type of lock supported by this engine is the whole table lock type.