MySQL database update sub-query, mysql database update

MySQL database update sub-query, mysql database update

For example:

UPDATE test. tb_vobile
Set a. name = '20140901'
WHERE
A. id = (select max (id) id from test. tb_vobile)

Error:

[SQL] UPDATE test. tb_vobile
Set a. name = '20140901'
WHERE
A. id = (select max (id) id from test. tb_vobile)


[Err] 1093-You can't specify target table 'A' for update in FROM clause

You can:

UPDATE test. tb_vobile
Join
(Select max (id) id from test. tb_vobile) B
On a. id = B. id
Set a. name = '20140901 ';
Or

UPDATE test. tb_vobile a, (select max (id) id from test. tb_vobile) B
Set a. name = '20140901'
WHERE
A. id = B. id;

Note:

1. During update, the updated table cannot be used in set and where for subqueries;
2. During update, you can update multiple tables (not SQL Server );
For example, update ta a, tb B set a. Bid = B. id, B. Aid = a. id;
3. Any query can be performed after update, which is equivalent to from;