I have a query that is updating a field in my table. It could be the case that that column to be updated can be NULL if that's the case i'd like to add 1 to that cell. Otherwise i'd like to add 1 to the current value of that field.
UPDATE SET Scheduled = Scheduled + 1
Would work except when cell's have NULL as their value, it does not add the 1 value.
You can use this.
UPDATE table SET Scheduled = ISNULL(Scheduled,0) + 1
You could use CASE expression:
UPDATE table_name
SET Scheduled = CASE WHEN Scheduled IS NULL THEN 1
ELSE Scheduled + 1
END
WHERE ...;
Although you can easily do this in the update:
update t
set scheduled = coalesce(scheduled + 1, 1)
where . . .;
I would suggest removing the need for it, by defaulting the value to 0. I suspect that will make sense in your context. If you have data in the table:
update t
set scheduled = 0
where scheduled is null;
alter table t alter scheduled int not null default 0;
(Note: You can also use with values in the alter, but the update clearly shows the intent.)
Update yourTable set yourColumn=(coalesce(yourColumn,0)+1)
Or you can use
Update yourTable set yourColumn=(nullif(yourColumn,0)+1)
