MySQL:按选择条件更新

Question

我有问答系统，每个问题应该有4个答案。

如果问题的答案少于4个，我需要运行SQL查询来将问题的“类”更新为"D“(以防止它出现)

表格结构：

问题(表)

id     text     class
1      Is..      A
2      Where..   B
3      where..   A
4      Why..     A 
5      Do..      A

回答(表)

id     qid     text
1       1       ..
2       1       ..
3       1       ..
4       2       ..
5       3       ..

注："answer.qid“指"question.id”

谢谢,

Answer 1

尝试一些具有子查询的内容，如：

UPDATE question
SET class = 'D'
WHERE id IN 
         (SELECT qid
          FROM answer
          GROUP BY qid
          HAVING count(qid) < 4)

Answer 2

我想说的是，您可以使用子选择

UPDATE question SET class='D' WHERE ( SELECT count(*) FROM answer INNER JOIN answer.qid = question.id ) < 4;

Answer 3

update question
 set class='D'
 where question.id in (select qid from answer group by qid having count(*)<4)