CODEWITHSUNDEEP
sqlsql-server-2012
user3010406
user3010406

Reputation: 579

Sum Values Of Field, Update Value In Other Field

I have a SQL table similar to the below:

Mon.  Tues.  Wed.  Thur.  Friday.  Sat.  Sun.  TotalHours
1       3     4     3      6       NULL     8      NULL
4       NULL  3     4      5        0     0      NULL

I am trying to correct the TotalHours value for each record by summing the values of remaining "Day" columns.

I have tried the following:

UPDATE [TestDatabase].[dbo].[Timekeeping]
SET [TotalHours] = [Sunday] + Monday + Tuesday + Wednesday + THursday + Friday + Saturday
WHERE [TotalHours] is null
GO

And the query does not error, and returns the amount of affected rows, however the change to the Hours value never changes. Is this because of some of the records having null values?

Upvotes: 0

Views: 35

Answers (1)

void
void

Reputation: 7890

Is this because of some of the records having null values?

Yes.

use isnull function, because null+value is null:

UPDATE [TestDatabase].[dbo].[Timekeeping]
SET [TotalHours] = isnull([Sunday],0) + isnull(Monday,0) + isnull(Tuesday,0) + isnull(Wednesday,0) + isnull(THursday,0) + isnull(Friday,0) + isnull(Saturday,0)
WHERE [TotalHours] is null

Upvotes: 3

Related Questions