MySQL SELECT updates the same table at the same time. problem occurrence and Solution

MySQL does not allow the select from statement to point to the table used for UPDATE. Of course, there is a better way than creating an endless temporary table. This article explains how to UPDATE a table and use SELECT in the query clause.

Problem description
Assume that the table I want to UPDATE is the same as the query clause. There are many reasons for this, for example, updating the table fields with statistical data (in this case, the group clause must be used to return the statistical value ), update another record from the field of a record without using non-standard statements. For example:Copy codeThe Code is as follows: create table apples (variety char (10) primary key, price int );
Insert into apples values ('Fuji', 5), ('Gala', 6 );
Update apples
Set price = (select price from apples where variety = 'Gala ')
Where variety = 'Fuji ';

ERROR message: ERROR 1093 (HY000): You can't specify target table 'append' for update in FROM clause. mySQL manual UPDATE documentation: "Currently, you cannot update a table and select from the same table in a subquery."
In this example, it is very easy to solve the problem, but sometimes you have to update the target through the query clause. Fortunately, we have a solution.

Solution
Since MySQL uses a temporary table to implement nested queries in The FROM clause, you can add nested queries to another nested query so that the FROM clause can be queried and saved in the temporary table, then indirectly referenced in the peripheral query. The following statement is correct:Copy codeThe Code is as follows: update apples
Set price = (
Select price from (
Select * from apples
) As x
Where variety = 'Gala ')
Where variety = 'Fuji ';

If you want to learn more about the mechanism, read the MySQL Internals Manual chapter.

Unsolved Problems
A common problem is that the IN () clause optimizes waste products and is rewritten into related nested queries. Sometimes (often ?) This causes low performance. Adding a nested query to another nested query does not prevent it from being rewritten into related nesting, unless I try my best. In this case, it is best to use JOIN to reconstruct the query (rewrite such a query as a join ).

Another unsolved problem is that the temporary table is referenced multiple times. The nested Query Technique cannot solve these problems because they are created during compilation, and the update problem discussed above is at runtime.