mysql 满足子查询的全部结果_mysql – 根据子查询的结果更新表中的多行

我有以下查询：

SELECT

stat.mcq_id,ROUND( stat.total_score / stat.num_taken,2 ) AS avg_score

FROM (

SELECT

user_mcq.mcq_id,SUM( score ) AS total_score,COUNT( user_mcq.id ) AS num_taken

FROM user_mcq

INNER JOIN user ON ( user.id = user_mcq.user_id )

WHERE user.level_id =3

AND user_mcq.is_complete =1

GROUP BY user_mcq.mcq_id

) AS stat

这会产生：

mcq_id avg_score

1 5.75

2 9.22

6 8.81

7 8.94

14 7.00

16 9.46

我想使用它来更新另一个名为mcq的表,使用结果中的mcq_id来匹配mcq.id

我尝试了以下,但没有成功：

UPDATE mcq SET mcq.avg_score = stats.avg_score FROM (

SELECT

stat.mcq_id,COUNT( user_mcq.id ) AS num_taken

FROM user_mcq

INNER JOIN user ON ( user.id = user_mcq.user_id )

WHERE user.level_id =3

AND user_mcq.is_complete =1

GROUP BY user_mcq.mcq_id

) AS stat

) AS stats

WHERE mcq.id = stats.mcq_id;

这给出了：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ( SELECT stat.mcq_id,2 ) A' at line 1