MYSQL Update using SUM the same table

Question

Hi so sorry for I know this just to basic. simple update only using sum on the same table. I need to get

total_tbl 
+-- month1 --- month2 --- month3 --- total --+ 
|     3          3          5                |
|     5          3          5                |
|     3                     4                |
|                5          5                |
+--------------------------------------------+

I need update the total column using SUM.

I have this statement so far:

UPDATE total_tbl SET total = (SELECT SUM(month1,month2,month3))

I should update even if one column doesn't have a value. Thanks!

Barmar · Accepted Answer · 2020-12-22 01:20:15Z

2

SUM() is used to sum an expression across multiple rows, usually using GROUP BY. If you want to add expressions in the same row you just use ordinary addition.

Use COALESCE() to provide a default value for null columns.

UPDATE total_tbl
SET total = COALESCE(month1, 0) + COALESCE(month2, 0) + COALESCE(month3, 0)

answered Dec 22, 2020 at 1:20

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-12-22 01:22:57Z

1

You shouldn't need to store this derived information. I would recommend a computed column:

alter table total_tbl
    add column total int -- or the datatype you need
    generated always as (coalesce(month1, 0) + coalesce(month2, 0) + coalesce(month3, 0)) stored

The additional column gives you an always up-to-date perspective at your data. You can even index it of you like, so it can be queried efficiently.

On the other hand, manually maintaining the values would require updating that column every time a value changes on the row, which can get tedious.

answered Dec 22, 2020 at 1:22

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

MYSQL Update using SUM the same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related