mysql - 从子查询的计数中更新值

Question

我正在尝试计算用户获得的所有正确答案并在分数表中更新它。但它给了我一个有趣的小错误。

UPDATE score
SET end_time = CURTIME(), final_score = COUNT(
    SELECT *
    FROM answered
    WHERE (exam_id = 8 and student_login = 'user' and guessed = 1)
)
WHERE exam_id = 8 and student_login = 'user'

错误:

#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 'SELECT * FROM answered WHERE (exam_id = 8 and student_login = 'user' and gue' at line 3

这应该很简单吧？那么这个错误是怎么回事呢？选择自己工作。并且更新在没有 final_score 更新的情况下工作。

感谢您的帮助!

Answer 1

UPDATE score
SET end_time = CURTIME(), final_score = (
    SELECT COUNT(*)
    FROM answered
    WHERE (exam_id = 8 and student_login = 'user' and guessed = 1)
)
WHERE exam_id = 8 and student_login = 'user'

应该这样做。

COUNT 不能(据我所知)对子查询进行操作。