Update table from calculated temporary table

Question

I have two tables, one containing a set of user ID's, vote direction (-1 or 1) and ID of the object they voted on (casted votes table). In the other table, the object id, upvotes, downvotes and totalvotes.

I'm trying to write a command to update the values in the second table by tallying the votes in the table of votes cast. I'm trying to think of the best way to do this, currently I am thinking of generating a temporary table that contains Object Id, upvotes, downvotes and totalvotes from the votes cast table (using a few SUM functions and some other stuff to generate the columns), then using an update query to go through and change the values in the second table by referencing the temporary table.

Is this the best way to do it or am I massively overlooking a feature that does this already without all the messing around in MySQL?

Answer 1

This might be a good case for using an update trigger.

Look at some of the examples here: http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

What you'll basically do is create a trigger on the first table that iterates a matching row in the second table when a row is added to the first table (on insert).