CODEWITHSUNDEEP
mysql
BeetleJuice
BeetleJuice

Reputation: 40916

mySQL: compute user reputation from comments table and update users table

I have two MySQL tables: comments

CommentID   UserID  Score
1           10      -5
2           10      2
3           11      3
4           11      0
5           12      2

... and users

UserID  CommentsReputation
10      0
11      0

I'm having trouble coming up with one efficient MySQL query that will set each user's CommentsReputation in the 2nd table to be the sum of all scores for comments belonging to that user. The challenge is that the only input I have with which to build the query is the CommentID (meaning I don't know the UserID at the code location where the query will be executed).

For example, given CommentID=3, I want to add up all the scores for UserID 11 and set that user's CommentsReputation to 3+0+2

Here is what I have so far:

UPDATE `user` u SET u.`CommentsReputation` = (
    SELECT SUM(c.`Score`) FROM `comments` c
    WHERE c.`UserID`= (
      SELECT c2.`UserID` FROM `comments` c2
      WHERE c2.`CommentID`= 3
    )
)

This will correctly select the sum of scores belonging to user #11, but it does not update the correct row in the users table

I know I could split this into one query to select the UserID at CommentID=3, and a second query with knowledge of the UserID. Hoping for a more efficient approach.

Upvotes: 1

Views: 41

Answers (1)

shmosel
shmosel

Reputation: 50726

This should do it:

UPDATE users u
  JOIN (
      SELECT UserID, SUM(Score) AS TotalScore
        FROM comments
       WHERE UserID = (SELECT UserID FROM comments WHERE CommentID = 3)
     ) c ON c.UserID = u.UserID
   SET u.CommentsReputation = c.TotalScore;

SQL Fiddle

If you want to update all the users at once, you can use a similar subquery with a GROUP BY:

UPDATE users u
  JOIN (
      SELECT UserID, SUM(Score) AS TotalScore
        FROM comments
       GROUP BY UserID
     ) c ON c.UserID = u.UserID
   SET u.CommentsReputation = c.TotalScore;

SQL Fiddle

Upvotes: 1

Related Questions