IsNull（）sql函数(IsNull() sql function)

IsNull（）sql函数(IsNull() sql function)

我正在尝试将名为“Number”的列字段的值增加1，如果该值当前为Null，我想将该值设置为1，因为Null值无法递增。 我发现了isNull（）函数，并且没有使用以下语句获得结果：

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

我的问题基本上是如何通过1的增量同时更新一个字段值，如果它当前为“空”，则将该值设置为1

谢谢！

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!

最满意答案

从ISNULL函数的第二个参数中删除Number 。

任何+ NULL = NULL

因此，当它为NULL时，使ISNULL结果为0 ，然后将1加到结果中

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

要么

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

或两个不同的更新（不推荐）

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;

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;