CODEWITHSUNDEEP
mysql
user2151960
user2151960

Reputation: 185

MySQL SUM cells with same ID and insert it in table

please help my with that: I have this table:

id      key         value
1       term1       35
1       term2       
1       term3       40

2       term1       12
2       term2       
2       term3       11

3       term1       51
3       term2       
3       term3       23

Each id has in the key column term2 which is sum of term1 and term3. And that sum should be inserted in value column. Like this:

id      key         value
1       term1       35
1       term2       75
1       term3       40

2       term1       12
2       term2       23
2       term3       11

3       term1       51
3       term2       74
3       term3       23

Thank you!

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

If you want to update the table, use join:

update table t join
       (select id, sum(value) as sumvalue
        from table t
        where key in ('term1', 'term3')
        group by id
       ) tt
       on t.id = tt.id
    set t.value = tt.sumvalue
    where t.key = 'term1';

Note that key is a keyword in MySQL (and SQL in general). I'm keeping the name, because that is how the question is phrased.

Upvotes: 1

Related Questions