Ravi Kumar. wrote:
> Dear Friends,
>
> I have two tables: T1, T2.
>
> T1 has 3 columns: playerid, gameid, score
> T2 has 2 columns: playerid, totalscore.
>
> I wish to update table T2 such that sum of T1.score of each player, gets
> updated in T2.totalscore. It may be something like this:
>
> update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
> T2.playerid
>
> OR
>
> update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
> T2.playerid group by playerid
>
> However none of the above is working.
>
> Where am I wrong? Please help.
>
> The version of MySQL I am using is 4.1.14-standard-log.
>
> Thanks,
>
> Ravi.
>
>
First I assunme you've done
INSERT INTO T2 SELECT DISTINCT(playerid), NULL FROM T1;
Since you need an aggregate function like SUM() and it needs GROUP BY
alas manual says "For the multiple-table syntax, UPDATE ... In this
case, ORDER BY and LIMIT cannot be used.":
http://dev.mysql.com/doc/refman/4.1/en/update.html
so I suppose you could try s.th. like this:
1. In code update each T2.totalscore in separate query using
SELECT SUM(score) as totalscore FROM T1 GROUP BY playerid;
Traverse resultset and in code (perl/php/whatever) update each
T2.totalscore with result
2. OR you can try second approach
UPDATE T2 SET totalscore=0
then you could try this
UPDATE T2 JOIN T1 ON T2.playerid=T1.playerid
SET T2.totalscore = T2.totalscore + COALESCE(T1.score, 0);
3. If your version supports subqueries... well then it is easy ;-)
If you want to update a table based on an aggregate function applied to
another table, you can use a correlated subquery, for example:
UPDATE T2
SET totalscore =
(SELECT SUM(T1.score) FROM T1 WHERE T2.playerid=T1.playerid)
Additional information on MySQL correlated subqueries is at
http://dev.mysql.com/doc/mysql/en/correlated-subqueries.html
HTH :-)
