Example analysis of MySQL implementing simultaneous query and update of the same table

This article mainly introduces the method of simultaneously querying and updating the same table in MySQL database. Friends who need it can refer to it

In ordinary projects, we often encounter such problems: I need to The data is updated and queried simultaneously in Zhang Biao. For example: There is a table data as shown below. Now the update operation needs to be: update the name value of status=1 to the name value of id=2

Normally we The following statement will come to mind to achieve this requirement:

UPDATE tb_testSET NAME = ( SELECT NAME FROM tb_test WHERE id= 2)WHERE `status` = 1

The result is an error, the error message is: You can't specify target table 'tb_test' for update in FROM clause, cannot be in the same statement In update, select the same table. You cannot operate on the same table. If you think about it differently, it should be possible if it is not the same table. Therefore, the select result can be regarded as a temporary intermediate table, and the desired update-related data can be obtained from the intermediate table. Therefore, the above update statement can be changed to the following:

UPDATE tb_testSET NAME = (select name from ( SELECT NAME FROM tb_test WHERE id = 2) as t)WHERE `status` = 1

In this way, the operation in the question can be completed. The general process is: query the data with id=2 as the intermediate table t; query the set data from the t table; do the update operation so that you are not updating and selecting the same table in the same statement, because this is quite similar to Two tables are being operated, tb_test and intermediate table t. The final result is as shown below:

The above is the detailed content of Example analysis of MySQL implementing simultaneous query and update of the same table. For more information, please follow other related articles on the PHP Chinese website!