MySQL does not allow SELECTFROM to be followed by a table used for UPDATE, which is sometimes confusing. Of course, there is a better way than creating an endless temporary table. This article explains how to UPDATE
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
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:
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:
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.