DELETE FROM `takes` WHERE ID, course_ID IN
(SELECT ID, course_ID FROM `takes` natural join `student`
where name = 'Jane' and course_ID='BIOL101')
从
takes
表中,我试图删除所有注册BIOL101的Janes。SELECT给了我一组正确的id和course_id,我只是试图从
takes
表中删除它。找不出正确的语法。phpMyAdmin给出了错误:“#1093-不能为FROM子句中的update指定目标表'takes'”
最佳答案
不要考虑[ab]使用IN
,而是使用MySQL支持的适当DELETE..JOIN
。
DELETE takes
FROM takes
JOIN student s
-- The relation/schema is unclear; adapt as required
-- but MAKE SURE A RELATION IS ESTABLISHED!
ON s.student_id = takes.student_id
WHERE s.name = 'Jane'
AND takes.course_ID = 'BIOL101'
另请参见this answer。
关于mysql - 使用一个自然表与另一个表联接的字段从一个表中删除MySQL?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23842527/