Constraint on table change with subquery

The only data closest to an explanation provided in the own documentation 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 ...);

Thinking this way, the example above works because, when faced with a "sub-subselect", the optimizer decides materialize query in temporary table: the record that receives read lock is a copy, in another location, of the one that will receive exclusive lock to UPDATE, avoiding conflict.

That said, I recognize that this picture I created does not make much sense = /. The InnoDB documentation is well detailed when stressing that the engine supports multiversioning of tuples and the four levels of lock isolation, you do not have to do this type of operation normally as in PostgreSQL, Oracle, and the like. I can only believe that this is a remnant of MyISAM implementation since pre-versions 5.5.5, since the only type of lock supported by this engine is the whole table.

Informix also has this restriction.

I'd say it's for the sake of implementation itself. At the bottom in the background is the danger of entering an loop infinity.

To avoid the error, I usually put the input data into a temporary table and work the UPDATE/DELETE/INSERT on top of that temporary table.

For more functions that MySql has, it was created to be a lightweight bank, and whether or not it would suffer some "losses" from its competitors.

And your documentation explains this lack as follows:

  

In MySQL, you can not modify a table and select from the same table in   to subquery. This applies to statements such as DELETE, INSERT,   REPLACE, UPDATE, and (because subqueries can be used in the SET   clause) LOAD DATA INFILE.

     

In MySQL, you can not modify a table and select from the same table   table in a subquery. This applies to statements such as DELETE,   INSERT, REPLACE, UPDATE, and (because subqueries can be used in   SET clause) LOAD DATA INFILE.

link

In summary Join is the most "current" method, the difference is that join has much more optimization and speed than subquery.

  

LEFT [OUTER] JOIN can be faster than an equivalent subquery because   the server might be able to optimize it better-a fact that is not   specific to MySQL Server alone. Prior to SQL-92, outer joins did not   exist, are only subqueries of the certain things. Today,   MySQL Server and many other modern database systems offer a wide range   of outer join types.    link

-

  

Subquery is the logical way to solve problems   of the form, "Get facts of A, conditioned to the facts of B".   In these cases, it makes more logical sense to keep B in a   subquery than to make an association. It is also safer in a   practical sense, that you do not have to be cautious about getting   of duplicate facts of A due to multiple matches against B.

     

Practically speaking, however, the answer usually comes down to   performance. Some optimizers suck lemons when they receive a join   vs a subquery, and some suck lemons on the other side, and that's   specific optimizer-specific, DBMS-version-specific and query-specific.

     

Historically, explicit unions generally win, hence   established wisdom is best, but optimizers are   improving all the time, and so I prefer to write queries   first in a logically coherent way, and then,   restructure if performance constraints ensure this.    link