Update SUM of a column Group by

UPDATE table_name SET col3 = SUM(col2) GROUP BY col1

Is giving an error:

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 col1'

How to solve this?

Col1  | col2  |   col3
Water |   22  |
water |   3   |
water |   5   |
Air   |  10   |
Earth |  3    |
Air   |  5    | 

I want in col3 the results as follows

Col1  | col2  |   col3
Water |   22  |   30
water |   3   |   30
water |   5   |   30
Air   |  10   |   15
Earth |  3    |   3
Air   |  5    |   5

subquery

UPDATE table_name SET col3 = (SELECT SUM(x.col2) FROM (SELECT * FROM table_name) AS x WHERE x.col1 =  table_name.col1)

this will do a subquery for each row updated getting the sum of col2 for every row that matches col1

the question is, why would you want to do this? you could always just use SUM to get this info if needed.

You could write the query as:

UPDATE test_table t1
INNER JOIN (
                SELECT col1, SUM(col2) AS sum_col2 FROM test_table GROUP BY col1
           ) t2
ON  t1.col1  =  t2.col1
SET t1.col3  =  t2.sum_col2;

Working demo

UPDATE table_name SET col3 = (Select SUM(col2) From table_name where col1=col1)