IsNull() sql function

Question

I am attempting increment the value of a column field named "Number" by 1, and if the value is currently Null I would like to set the value to 1 because a Null value cannot be incremented. I discovered the isNull() function and do not get the results using the following statement:

Update SomeTable set Number = IsNull(Number, Number+ 1) where
ItemCode = '000000' ;

My question is basically how to simultaneously update a field value by the increment of 1 and set the value to 1 if it is currently "NULL"

Thanks!

Answer 1

Remove Number from second parameter of ISNULL function.

Anything + NULL = NULL

so make the ISNULL to result 0 when it is NULL and then add 1 to the result

Update SomeTable set Number = IsNull(Number, 0) + 1 where
ItemCode = '000000' ;

or

Update SomeTable set Number = IsNull(Number+1, 1) where
ItemCode = '000000' ;

or two different updates (not recommended)

Update SomeTable set Number = Number + 1 where
ItemCode = '000000' AND Number IS NOT NULL;

Update SomeTable set Number = 1 where
ItemCode = '000000' AND Number IS NULL;