Tally up a table and put the results in a different table

[Dani]

I'm having a brain fart and for some reason it's not coming to me how to do this right now.

I'm trying to tally up a table, and store the results in a different table. Like so

user     score
----     -----
1        4
1        5
1        2
2        1
2        3
...

And now I want to tally that up and put it into the following table:

user     total_score
----     -----------
1        ?
2        ?

I know it can be done with a single MySQL query with a subquery, but I didn't get much sleep last night :)

INSERT INTO table2 (user, total_score)
SELECT
    (
        user, SUM(score)
        FROM table1
        GROUP BY user
    )

But wait ... what if I just wanted to update the total_score column??

What's the proper syntax to update existing rows where user is a primary key in both tables?

[BitBlt]

This might work. I didn't get a chance to test it on mySQL but it uses ANSI standard SQL and it works in MSSQL just fine...you might have to tweak it because of the square brackets in the name since "user" is a reserved word in MSSQL.

update a 
set a.total_score = b.total_score
from table_2 a
inner join 
    (
    select [user], sum(score) as total_score
    from table_1
    group by [user]
    ) b
    on a.[user] = b.[user]

Hope this helps! Good luck!

[cereal]

Also, you could try with insert ... select ... on duplicate key, an example query:

insert into table1 (user, score) values(1,2);

insert into table2 (user, total_score) select user, score from table1 where id = last_insert_id() on duplicate key update total_score = total_score + score;

The conditions are:

• you cannot use group by on the select segment
• you need an auto increment key in table1 to get the last_insert_id()

• you cannot perform multiple inserts into table1 like this:

  insert into table1 (user, score) values(1,7), (1,3), (1,4);

because last_insert_id() will refer only to the first of these and the second query will continue to add 7.

An example: http://sqlfiddle.com/#!2/7e6e58/1

Ref. http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

[rch1231]

I usually go for the subquery version and I believe this will work:

update table2
set total score = (select sum(score) from table1 where table1.id = table2.id)

[urtrivedi]

Dani, I guess update queries will be in batch mode and not concurrent. So to get concurrent result, I suggest to write insert/update/delete database trigger on base table to keep summary table upto date

[Dani]

Sorry, I didn't have a chance to check this out yet. I will very shortly.

[Dani]

Rch1231's suggestion worked like a charm!! :)

After being plagued by server issues, our counters got a little out of sync, and I needed to recalculate all of the statistics for all of the members.