MySQL/PHP updating a value in a variable number of rows

Question

I have an users table where I store user info along with a reputation score like Stackoverflow has.

My question is how to update multiple users' reputations with a single MySQL query.

Here's my users table

user_id  reputation
  1          11
  2         202
  3        3003
  4         444
  5         555

Let's say user_id=1 downvotes user_id=4 and user_id=5. In this scenario user_id=1's reputation loses 2 points and user_id=4,5 both lose 10 points.

The users table would then look like this:

user_id  reputation
  1           9 //lost 2 points
  2         202
  3        3003
  4         434 //lost 10 points
  5         545 //lost 10 points

I know the MySQL query shown below works but I'm wondering a generalizable solution in the case that the number of users to UPDATE is variable and depends on the voting.

UPDATE `users`
 SET `reputation` = CASE
 WHEN `user_id` = '1' THEN reputation - 2
 WHEN `user_id` = '4' THEN reputation - 10
 WHEN `user_id` = '5' THEN reputation - 10
 ELSE `reputation` END

I would create temporary table with your current data. Then do an inner join to update the records at once. Then again you are faced with the issue of how to define the 'update factor' of your reputation e.g. -2 , -10, +5... so on. UPDATE tblUsers u JOIN temp_tblUsers t ON (u.ID=T.ID) SET p.REPUTATION = t.REPUTATION -2; — bonCodigo
– bonCodigo, Commented Nov 11, 2012 at 15:06

Michael Berkowski · Accepted Answer · 2012-11-11 15:04:21Z

3

You can replace the second WHEN with an IN() clause. It still requires some dynamic query building in your application code, but if your API allows direct binding of arrays into an IN() clause, this is much easier.

UPDATE `users`
 SET `reputation` = CASE
 WHEN `user_id` = '1' THEN reputation - 2
 WHEN `user_id` IN ('2','3','4') THEN reputation - 10
 ELSE `reputation` END

I will admit I find it a bit awkward to force all of this into one query though. Unless performance is a real issue, I would rather see this as two queries wrapped in a transaction - one to subtract -2 from user_id=1 and one to subtract the -10 users.

/* Only on InnoDB tables... */
START TRANSACTION;
Update `users` SET `reputation` = `reputation` - 2 WHERE `user_id` = 1;
Update `users` SET `reputation` = `reputation` - 10 WHERE `user_id` IN (2,3,4);
COMMIT;

Documentation on MySQL transaction handling...

edited Nov 11, 2012 at 15:04

answered Nov 11, 2012 at 14:55

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tim peterson Over a year ago

-@Michael thanks, yes using PHP I can just implode() the user_id array to make the string for the IN clause. Checking right now to see if your query makes sense within my application...

tim peterson Over a year ago

-@Michael, i'm not sure what a transaction is. Would you mind including the two queries solution you suggest in your answer?

Michael Berkowski Over a year ago

@timpeterson Added. Your way works fine, the logic is just confusing to follow on frist glance.

bonCodigo Over a year ago

In this method how do you tweak it if you have totally different reputation factors to be subscracted?

Collectives™ on Stack Overflow

MySQL/PHP updating a value in a variable number of rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related