MySQL--You can‘t specify target table ‘u‘ for update in FROM clause

You can’t specify target table ‘u’ for update in FROM clause

在MySQL里，不能先查询某个表中的记录，再在此条件上进行更新或者删除这个表的记录

例如：

update user as u set u.username='xiaoqing' where u.username='xiaoxiao'  and (select us.U_id as user_id from user as us where us.username='xiaoxiao')=any(select s.student_id from student as s where s.student_classid='20210504')

解决办法：
将查询到的结果，再通过中间表查询一遍
1.先查询所需要的语句结果

select us.U_id as user_id from user as us where us.username='xiaoxiao'

2.在此基础上再进行一次查询

select user_id from(select us.U_id as user_id from user as us where us.username='xiaoxiao')as b

3.进行更新操作

update user as u set u.username='xiaoqing' where u.username='xiaoxiao'  and (select user_id from(select us.U_id as user_id from user as us where us.username='xiaoxiao')as b)=any(select s.student_id from student as s where s.student_classid='20210504')