updating totals with one sql query

Question

I have a table with players, and a table with profiles. Profiles(users) can "claim" players, so that the "profile" field in a player record gets set to the id of the profile.

I want a totalscore property(which contains the sum of all previous score values) in my profiles table. To calculate data commited in the past, i've written the following query:

UPDATE profiles,players
SET profiles.`totalscore` = profiles.`totalscore` + players.`score`
WHERE players.`profile`=profiles.`id`

However, totalscore gets set to the last found value. How would i solve this?

Please, provide sample input data and desired result (now it's not clear) — Alma Do, Sep 10 '13 at 08:47

Chamal · Accepted Answer · 2013-09-10T09:08:49.087

0

You have to use group by.

UPDATE profiles
inner join (
select profileid,SUM(score) as playersscore
from players
group by
profileid) players
ON players.profileid=profiles.id
SET profiles.totalscore = profiles.totalscore + playersscore

edited Sep 10 '13 at 09:08

answered Sep 10 '13 at 08:47

Chamal

1,439
10
15

result: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY profiles.`id`' at line 4 – Oht Sep 10 '13 at 09:52

updating totals with one sql query

1 Answers1