Where子句中的未知列:Mysql Update Query

Question

这是我的更新查询:

update grade 
set grade='A'
where (select assignment*0.3+midExam*0.35+finalExam*0.35 as FINALSCORE 
from taking) >= 85 
and taking.student_id = grade.student_id 
and taking.subject_id = grade.subject-id;

，结果低于

未知列占用。Student_id ' in 'where子句'

我做错了什么?

Answer 1

表taking在子查询内，然后在子查询外不可见

对于您的需要，您应该使用join更新

update grade 
INNER JOIN taking ON taking.student_id=grade.student_id
and taking.subject_id=grade.subject_id 
and taking.assignment*0.3+taking.midExam*0.35+taking.finalExam*0.35 >= 85
set grade='A'

的通解

你可以试着为每个等级

添加一个case

update grade 
INNER JOIN taking ON taking.student_id=grade.student_id
and taking.subject_id=grade.subject_id          
set grade = (case when taking.assignment*0.3+taking.midExam*0.35+taking.finalExam*0.35 >= 85 THEN  'A'
when taking.assignment*0.3+taking.midExam*0.35+taking.finalExam*0.35 between 60.0 and 84.9 THEN 'B'
ELSE 'C' END)