mysql update 更新使用 join on关联

格式

update  table  t1 (left) join   table2 t2 on  ( t1.id=t2.pid )set ti.fiele='' where t2.filed=''

sql联系题

把“score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

sql语句

update score sc
join
  (select course_id,round(avg(score),2)  avg from score sc join  course cou on sc.course_id = cou.id 
  join teacher t on cou.t_id = t.id where t.t_name='叶平' group by course_id) res
on res.course_id =sc.course_id
set sc.score = res.avg where sc.course_id = res.course_id;

详解
1.
select course_id,round(avg(score),2) avg
from score sc join course cou on sc.course_id = cou.id
join teacher t on cou.t_id = t.id
where t.t_name=‘叶平’ group by course_id

通过上面的sql语句查出叶平老师所教的课程id和平均分

2.使用join on 关联，修改数据库