MySQL SELECT at the same time UPDATE same table problem occurred and resolved

MySQL does not allow SELECT FROM to be followed by a table used as UPDATE, which is sometimes confusing. Of course, there are better ways than creating endless temporary tables. This article explains how to UPDATE1 tables while using SELECT in the query clause.

Problem description
Let's say I want UPDATE's table to be the same table as the query clause. There are a number of reasons to do this, such as updating the table's fields with statistics (you need to return statistics with the group clause), starting from one record's field, update, and another record, instead of using non-standard statements, and so on. Here's an example:

 
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'; 

The error is: ERROR 1093 (HY000): You can t specify target table for update in FROM clause. MySQL manual UPDATE documentation "Currently, you cannot a a and from the same table in a subquery."
In this example, it is also 10 minutes easy to solve the problem, but sometimes you have to query the clause to find the update target. The good news is we have a solution.

The solution
Since MySQL implements nested queries in the FROM clause through temporary tables, loading a nested query into another nested query makes the FROM clause query and save both in the temporary table, and then indirectly referenced in the peripheral query. The following statement is correct:

 
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 mechanics, read the section on MySQL Internals Manual.

Unsolved problems
A common problem is that the IN() clause optimizes waste, is rewritten as a related nested query, and sometimes (often?) Resulting in poor performance. Loading a nested query into another nested query does not prevent it from being rewritten as a related nested query, unless I do something drastic. In this case, it is best to refactor the query (rewrite such a query as a join) with JOIN.

Another unresolved problem is that temporary tables are referenced multiple times. The "insert nested queries" technique does not solve these problems because they are created at compile time, whereas the update problem discussed above is at run time.