How to perform update operation in MySQL database at the same time as select
- 黄舟Original
- 2016-12-24 17:25:132655browse
MySQL does not allow SELECT FROM to point to the table used for UPDATE, which is sometimes confusing. Of course, there are better ways than creating endless temporary tables. This article explains how to UPDATE a table while using SELECT in the query clause.
Problem description
Suppose the table I want to UPDATE is the same table as the query clause. There are many reasons for doing this, such as updating the table with statistical data fields (at this time, the group clause needs to be used to return statistical values), update another record from the fields of a record without using non-standard statements, etc. 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';
The error message is: ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. MySQL ManualUPDATE documentation This is explained below: "Currently, you cannot update a table and select from the same table in a subquery." In this example, it is very simple to solve the problem, but sometimes you have to update the target through the query clause. Fortunately, we have a solution.
Solution
Since MySQL implements nested queries in the FROM clause through temporary tables, then loading the nested query into another nested query can make the FROM clause query and save in the temporary table is then indirectly referenced in the enclosing query. The following statement is correct:
update apples
set price = (
. The content of the database update method while selecting. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!