1

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?

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).

|improve this answer|||||
  • 1
    He didn't say that it should be done in the same transaction as an insert. The trigger may slow down the inserts, if it's a system with a bit more pressure on it. – Ionic Jun 17 '15 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.