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

最佳答案

我认为您可以使用与表的连接并更新列,如下所示：

 UPDATE mcq,(SELECT 
            stat.mcq_id,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
       ) AS stats
SET mcq.avg_score = stats.avg_score 
WHERE mcq.mcq_id = stats.mcq_id;

原文链接：https://www.f2er.com/mysql/433664.html

mysql – 根据子查询的结果更新表中的多行

猜你在找的MySQL相关文章