update与select嵌套

1.⽤b表的结果为a表赋值

update student set hobby= (select hobby_name from hobby )错误，提⽰“Subquery return more than one row\"

update student set hobby= (select hobby_name from hobby limit 1)执⾏ok

说明：查询结果应该是⼀个唯⼀字段才能给前⾯的a表字段赋值。

2.⽤⼀个表的查询结果给字段赋值

update student set hobby= (select hobby from student where studentid=1) where studentid=2错误，提⽰You can't specify target table 'student' for update in FROM clause

解决办法：把查询结果作为临时表b,再次查询赋值update student set hobby=(select b.hobby from

(select hobby from student where studentid=1) as b)

where studentid=2执⾏ok

3.⽤同⼀个表的查询结果作为条件

update student set status =2 where studentid in( select studentid from student where studentid<10 and status=0 order by studentid asc limit0,1)

错误，提⽰

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

update student set status =2 where studentid in( select studentid from student where studentid<10 and status=0 order by studentid asc )

错误，提⽰

You can't specify target table 'student' for update in FROM clause

解决办法：改变查询⽅式，

student status =2 where studentid<10 status=0 order by studentid asc limit 0,1 或

把查询结果作为临时表b

student a,( studentid from student where studentid<10 status=0 order by studentid asc limit 0,1) b a.status =2 where a.studentid=b.studentid

或连接

student a join ( studentid from student where studentid<10 status=0 order by studentid asc limit 0,1) b on a.studentid=b.studentid a.status=2