CODEWITHSUNDEEP
mysql
nenad007
nenad007

Reputation: 139

update value with sum of multiple column

I have a table power, where I wan got the SUM of all my is_* fields and store it in the cnt column.

id | is_yellow | is_green | is_blue | cnt
1  | 1         | 1        | null    | -> 2
2  | null      | 1        | null    | -> 1
3  | 1         | 1        | 1       | -> 3

The query give "Invalid use of group function" back on Update:

UPDATE
  power
SET
cnt = (SUM(IFNULL(is_yellow, 0)) + 
  SUM(IFNULL(is_green, 0)) + 
  SUM(IFNULL(is_blue, 0)))

What's wrong there?

Upvotes: 0

Views: 414

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use COALESCE for this:

UPDATE
  power
SET
  cnt = COALESCE(is_yellow, 0) +
        COALESCE(is_green, 0) +
        COALESCE(is_blue, 0)

If the field is null, then 0 is used in the addition.

Upvotes: 1

Related Questions