How to query and update the same table in MySQL database at the same time

In ordinary projects, I often encounter this problem: I need to update and query data in one bid at the same time. For example, if there is a table of data as shown below, the update operation now needs to be: update the name value of status=1 to the name value of id=2

Usually we think of the following statement to achieve this requirement:

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

However You can't specify target table 'tb_test' for update in FROM clause . You can't operate on the same table. Change your thinking. If it's not the same table, it should be possible. Therefore, you can treat the selected result as a temporary intermediate table and obtain the desired update-related data 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

This completes the operation in the question. The general process is: query the data of id=2 as the intermediate table t; query the set data from the t table; perform the update operation so that you do not update and select the same table in the same statement, because this is equivalent to operating two tables, tb_test and the intermediate table t. The final result is as follows: