Member Avatar for 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?

  • 5 Contributors
  • 6 Replies
  • 402 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

Two common needs here: do a one‑time full rebuild of the summary table, or keep the summary up‑to‑date continuously. ’s subquery approach is fine for a quick recalculation; for MySQL a compact, efficient single query to update existing rows uses an UPDATE with a JOIN to the aggregated results:

UPDATE table2 t
JOIN (
  SELECT user AS uid, SUM(score) AS total_score
  FROM table1
  GROUP BY user
) s ON t.user = s.uid
SET t.total_score = s.total_score;

If you want to insert missing users and update existing ones in one pass, use INSERT ... SELECT ... ON DUPLICATE KEY UPDATE (make sure user is a UNIQUE/PRIMARY KEY in table2):

INSERT INTO table2 (`user`, total_score)
SELECT user, SUM(score)
FROM table1
GROUP BY user
ON DUPLICATE KEY UPDATE total_score = VALUES(total_score);

That addresses the batch/refresh use case. ’s last_insert_id() trick is useful only for single-row flows; it doesn’t work reliably for multi-row or bulk inserts.

For real-time consistency, follow ’s advice and use triggers (or update totals in the application). Example AFTER INSERT trigger pattern:

DELIMITER $$
CREATE TRIGGER table1_after_insert
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table2 (`user`, total_score)
  VALUES (NEW.user, NEW.score)
  ON DUPLICATE KEY UPDATE total_score = total_score + NEW.score;
END$$
DELIMITER ;

Also add AFTER UPDATE and AFTER DELETE triggers to handle score changes and removals.

Extra tips: keep an index on the user column in both tables, run full rebuilds into a temp table then RENAME TABLE atomically if you need zero‑downtime recalculation, and avoid using reserved words for column names (or quote them with backticks). Back up the summary table before mass operations.

Recommended Answers

All 6 Replies

Member Avatar for 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!

Member Avatar for 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

Edited by cereal
Member Avatar for 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)

commented: Worked like a charm +15
Member Avatar for 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

Edited by urtrivedi
Member Avatar for Dani

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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.