MYSQL 1093 - You can't specify target table 报错


UPDATE course_member SET role = 'X' where 
userId = (SELECT id from user where nickname ='dm10001') 
and courseId in
(
SELECT id from course_set_v8 WHERE id in 
(SELECT courseId from course_member where userId = (SELECT id from user where nickname ='dm10001') and classroomId = 127)
and  FIND_IN_SET(REPLACE(tags,'|',''),'30,31,32,33,34') and tags !=''
)

1093 - You can't specify target table 'course_member' for update in FROM clause
时间: 0s

什么情况呢？为什么报错啊？
需要怎么修改？

意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中);改成这样试试

UPDATE course_member SET role = 'X' where 
userId in (SELECT a.id FROM (SELECT id from user where nickname ='dm10001') a)
and courseId in (SELECT b.id FROM 
(
SELECT id from course_set_v8 WHERE id in 
(SELECT courseId from course_member where userId = (SELECT id from user where nickname ='dm10001') and classroomId = 127)
and  FIND_IN_SET(REPLACE(tags,'|',''),'30,31,32,33,34') and tags !=''
) b
)

在同一语句中不能 先select出同一表中的某些值，再update这个表 ，可以使用子查询

UPDATE course_member SET role = 'X' where 
userId = (SELECT id from user where nickname ='dm10001') 
and courseId in
( SELECT ids from (
SELECT id ids from course_set_v8 WHERE id in 
(SELECT courseId from course_member where userId = (SELECT id from user where nickname ='dm10001') and classroomId = 127)
and  FIND_IN_SET(REPLACE(tags,'|',''),'30,31,32,33,34') and tags !='') as temp
)