Hello, there is a table of questions with the answers (int) field, and there is a table of answers with the field (question_id)

How can I update the answers field for all questions so that there are exactly as many entries as there are entries in the answers table with the questions_id = id field in the questions

  • And why do you need answers to the field? the existence of this field is redundant and must be constantly updated. in such cases it is customary not to create the field, but to calculate the quantity on the go. if you still want to do it, first make a query that will get the quantities in the q_id section (use group by and count ()) and then use the update form with join where to use the resulting query in the first stage - Mike

2 answers 2

If I'm not mistaken, you can do this:

UPDATE `questions` q SET q.`answers` = ( SELECT COUNT(`question_id`) FROM `answers` a WHERE a.`question_id` = q.`id` )

But as correctly noted by @Mike in the comments:

The existence of this field is redundant and must be constantly updated. in such cases it is customary not to create the field, but to calculate the quantity on the go.

  • but it is less than a load, at a conclusion of quantity, it is simple to choose value from a column, and not to do run on all base choosing number of records - turik97
 UPDATE questions q left join (select count(aa.id) as c, aa.questions_id from answers aa group by aa.questions_id) a on a.questions_id=q.id set q.answers=ac;

Test: http://sqlfiddle.com/#!9/f4c853/1