Table change constraint with subquery

Question

To documentation mysql 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 that SQL Server does not have this restriction. Anyone who knows about Postgres and Oracle says so 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 1

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

Extrapolating from this information, this behavior may be a result of the need to avoid conflicting Locks. An UPDATE from a SELECT becomes impossible because the engine does not allow you to obtain a unique lock for a record that is already locked from another transaction.

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

That said, I recognize that this picture I created doesn’t make much sense =/. A documentation of Innodb It is very detailed when emphasizing that the engine supports multiversioning of tuples and the four lock isolation levels, do not have to not 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-5.5.5 versions, since the only type of lock supported by this engine is the whole table.

Answer 2

Informix also has this restriction.

I would say that it is for the sake of the same implementation. In the background it is due to the danger of entering a loop infinite.

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

Answer 3

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

A 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, so Subqueries Were the only way to do Certain Things. Today, Mysql Server and Many other Modern database systems Offer a wide range of Outer Join types. https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html

-

Subquery is the logically correct way to solve problems from the form, "Get facts from A, subject to the facts of B". In such cases, it makes more logical sense to keep B in a subquery than making an association. It is also safer, in a practical sense, which you do not need to be cautious about getting of duplicated facts of A due to multiple matches against B.

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

Historically, explicit unions generally win, hence the established wisdom that unites is better, but the optimizers are improving all the time, and so I prefer to write the queries first in a logically coherent way and then restructure if performance constraints ensure this. https://stackoverflow.com/a/2577188/5703284

Answer 4

However many functions Mysql has, it was created to be a lightweight bank, and whether or not it would suffer some "losses" before its competitors.

And your documentation explains this lack as follows:

In Mysql, you cannot Modify a table and select from the same table in a 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 cannot modify a table and select from it table in a sub-query. This applies to statements like DELETE, INSERT, REPLACE, UPDATE and (because sub-consultations can be used in SET clause) LOAD DATA INFILE.

https://dev.mysql.com/doc/refman/5.7/en/subqueries.html