MySQL database queries and updates the same table at the same time.

MySQL database queries and updates the same table at the same time.

In normal projects, I often encounter the following problem: I need to update and query data simultaneously in a tag. For example, if there is a table data, the update operation is to update the name value of status = 1 to the name value of id = 2.

We usually think of the following statement to achieve this requirement:

UPDATE tb_testSET NAME = (select name from tb_test WHERE id = 2) WHERE 'status' = 1

An error is returned. The error message is:You can't specify target table 'tb _ test' for update in FROM clause, Cannot be updated in the same statement, select the same table. You cannot operate on the same table. You can use another method if not the same table. Therefore, you can use the select result as a temporary intermediate table to obtain the desired update 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

In this way, you can complete the operations in the question. The general process is as follows: query the data with id = 2 as the intermediate table t; query the set data from the t table; perform the update operation so that the data is not updated in the same statement, select the same table, because it is equivalent to the two tables in the operation, tb_test and intermediate table t. The final result is as follows: