mysql - compute value and auto insert in column

Question

I am not a frequent mysql user, but I do know commands enough to get my work done.

I have a table that has say 3 fields, Unique_Key, Number_cnt, Total_number.

The table is filled with unique key and number but total number is kept blank. Say table looks like this.

A | 10 | NULL
A | 25 | NULL
A | 33 | NULL
B | 13 | NULL
D | 11 | NULL
D | 22 | NULL

Is there a sql way of updating the last column with the sum of the numbers grouped by the unique key.

End table like this:

A | 10 | 68
A | 25 | 68
A | 33 | 68
B | 13 | 13
D | 11 | 33
D | 22 | 33

I do know the query to generate the result

select unique_key,sum(number_cnt) from mTable group by unique_key;

What I am looking for is a way to auto fill the column.

Thanks !

Answer 1

So, firstly I would avoid using the word Unique in a column name to describe data which is not Unique.

Secondly this could be easily computed on the fly with (This avoids having to update multiple records in the table each time you update/insert a single row):

SELECT a.Unique_Key, Number_cnt, Total_number
FROM mTable AS a
LEFT JOIN (
  SELECT Unique_Key, sum( Number_cnt) AS Total_number
  FROM mTable 
  GROUP BY Unique_Key
) AS b ON a.Unique_Key= b.Unique_Key

Finally if you do require to do this the following query should work:

UPDATE mTable AS a
INNER JOIN (
  SELECT Unique_Key, SUM(Number_cnt) as sum_count 
  FROM mTable GROUP BY Unique_Key) AS b 
ON a.Unique_Key=b.Unique_Key
SET a.Total_number=sum_count 

(Using the original table in the where clause, as suggested in some other answers, will throw a 1093 error - see MySQL Error 1093 - Can't specify target table for update in FROM clause)

Answer 2

Tested, working:

UPDATE mTable AS t1
INNER JOIN (
  SELECT Unique_Key, SUM(Number_cnt) AS Sum_count 
  FROM mTable
  GROUP BY Unique_Key) AS t2 
ON (t1.Unique_Key = t2.Unique_Key)
SET t1.Total_number = Sum_count