Schedule and update with count of a different table

Question

i have 3 tables like this

questions_table

question_id | content           |  user             |
1           | my first question | userOne@email.com |
2           | my second question | userTwo@email.com|

replies_table

reply_id | question_id|user                |content               |voteCount|
1        |      1     |userSeven@email.com |first reply question 1 |0       |
2        |      1     |userEight@email.com |second reply question1 |0       |

vote_table

vote_id  | reply_id|  voted_by          |
1        |      2  | userThree@email.com|
2        |      2  | userFour@email.com |

so to explain this:

1. A question was posted by two users userOne@email.com and UserTwo@email.com
2. Then userSeven@email.com and userEight@email.com replied to question 1.
3. Then the reply of userEight@email.com which has a reply_id of 2 was voted up by userThree@email.com and userFour@email.com

what i need to do is to write an event scheduler in myphpmyadmin which will run every 2 hours. what i want the query to do is to update the column voteCount in replies_table by counting the votes on that reply id. this is what i got so far

SELECT COUNT(voteCount)
FROM replies_table
WHERE reply_id = .../**dont know how am i suppose to do this part **/;

i know it would be something like this but i have never wrote a event scheduler

Answer 1

You may simply use join to get the total vote count.

  select r.reply_id, v.Countreply as count from replies_table as r 
  inner join 
  (select reply_id, count(reply_id) as CountReply from vote_table group by reply_id) as v 
  on r.reply_id= v.reply_id

If you want to update then it is simply done by update.

 update r set r.votecount = v.Countreply 
  from replies_table as r inner join 
  (select reply_id, count(reply_id) as CountReply from vote_table group by reply_id) as v 
  on r.reply_id= v.reply_id

Answer 2

In MySQL, you would express the update as:

i want the query to do is to update the column voteCount in replies_table by counting the votes on that reply id. this is what i got so far

update replies_table rt join
       (select reply_id, count(*) as numvotes
        from votes v
        group by reply_id
      ) v
    set rt.VoteCount = v.numvotes;

This may not be the best way to keep your data up-to-date.

If your tables are not very big, then there is no need to store the VoteCount separately. Just run the query when you need it.

If the VoteCount is actually important, then use a trigger to keep it up-to-date.

And, there is no need to update all the rows, even if you take this route. You can keep track of timestamps and the last time updated to limit the number of updates.