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;

Answer 2

ISNULL function choose the alternative value if the main value is null.

UPDATE SomeTable SET Number =
CASE
    WHEN Number IS NULL THEN 1 ELSE Number + 1
END 
WHERE ItemCode = '000000' ;

Or

UPDATE SomeTable SET Number = ISNULL(Number, 0) + 1
WHERE ItemCode = '000000' ;

Answer 3

The statement WHERE ItemCode = '000000'; will update all records with this item code: 000000. A simple update SomeTable set Number = 1 will solve your problem.

Answer 4

The easiest way is to use coalesce to provide a default value of 0 when NULL is found, as in

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