问题描述

这是我在做数据库作业的时候遇到的一个问题

表结构：

问题SQL：

delete
from student s
where s.id in
      (select student_course.student_id as id
       from student_course
                join student s2 on s2.id = student_course.student_id
       where s2.department_id = (select id
                                 from department
                                 where department.name = '经济管理学院')
         and student_course.score is null)

目的：删除经济管理学院考试成绩为NULL的学生记录。

具体报错：

[1093] You can't specify target table 's' for update in FROM clause

原因

在MySQL中不能先select出同一表中的某些值，再update这个表（在同一语句中），即不能依据某字段值做判断再来更新某字段的值

解决方法

把select出来的表再select一遍起个别名作为条件

delete
from student s
where s.id in (select id
               from (select student_course.student_id as id
                     from student_course
                              join student s2 on s2.id = student_course.student_id
                     where s2.department_id = (select id
                                               from department
                                               where department.name = '经济管理学院')
                       and student_course.score is null) as t)