MySQL数据库update更新子查询

mysqlupdate更新子查询。

比如：

update test.tb_vobile a set a.name = '111 ' where a.id = (select max(id) id from test.tb_vobile)

报错：

[sql]update test.tb_vobile a set a.name = '111 ' where a.id = (select max(id) id from test.tb_vobile)

以下可通过：

update test.tb_vobile a join (select max(id) id from test.tb_vobile) b on a.id = b.id set a.name = '123 '; 或 update test.tb_vobile a ,(select max(id) id from test.tb_vobile) b set a.name = '321 ' where a.id = b.id ;

说明：

1、update 时，更新的表不能在set和where中用于子查询;

2、update 时，可以对多个表进行更新(sqlserver不行);

如：update ta a,tb b set a.bid=b.id ,b.aid=a.id;

3、update 后面可以做任意的查询，这个作用等同于from;

update test.tb_vobile a

set a.name = '111 '

where

a.id = (select max(id) id from test.tb_vobile)

报错：

[sql]update test.tb_vobile a set a.name = '111 ' 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

以下可通过：

update test.tb_vobile a join (select max(id) id from test.tb_vobile) b on a.id = b.id set a.name = '123 '; 或 update test.tb_vobile a ,(select max(id) id from test.tb_vobile) b set a.name = '321 ' where a.id = b.id ;

update user_tb a,(select userid from user_tb where userid>1 order by userid limit 0,10) b

set a.nickname='哈哈哈' where a.userid in (b.userid);

说明：

1、update 时，更新的表不能在set和where中用于子查询;

2、update 时，可以对多个表进行更新(sqlserver不行);

如：update ta a,tb b set a.bid=b.id ,b.aid=a.id;

3、update 后面可以做任意的查询，这个作用等同于from;