update table1
set A = (select B from table1 where B=‘’)
MySQL手册UPDATE documentation这下面有说明 : “Currently, you cannot update a table and select from the same table in a subquery.”
分析:
MySQL是通过临时表来实现FROM子句里面的嵌套查询,那么把嵌套查询装进另外一个嵌套查询里,可使FROM子句查询和保存都是在临时表里进行,然后间接地在外围查询被引用。下面的语句是正确的:
update table1 set A = (select * from (select B from table1 where B=‘’) as t)