IsNull() sql 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41723647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IsNull() sql function
提问by D0uble0
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:
我正在尝试将名为“Number”的列字段的值增加 1，如果该值当前为 Null，我想将该值设置为 1，因为 Null 值无法增加。我发现了 isNull() 函数并且没有使用以下语句获得结果：
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"
我的问题基本上是如何以 1 的增量同时更新字段值，如果当前为“NULL”，则将该值设置为 1
Thanks!
谢谢！
回答by P?????
Remove Numberfrom second parameter of ISNULLfunction.
Number从ISNULL函数的第二个参数中删除。
Anything + NULL = NULL
任何 + NULL = NULL
so make the ISNULLto result 0when it is NULLand then add 1to the result
所以当它是ISNULL结果0时NULL，然后添加1到结果中
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;
回答by Jaider Xavier Jr.
The statement WHERE ItemCode = '000000';will update all records with this item code: 000000. A simple update SomeTable set Number = 1will solve your problem.
该语句WHERE ItemCode = '000000';将使用此项目代码更新所有记录：000000。一个简单的update SomeTable set Number = 1将解决您的问题。
回答by Joe Taras
ISNULLfunction choose the alternative value if the main value is null.
ISNULL如果主值为空，则函数选择替代值。
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' ;
回答by Mark Adelsberger
The easiest way is to use coalesceto provide a default value of 0 when NULL is found, as in
最简单的方法是在coalesce发现 NULL 时使用提供默认值 0，如
Update SomeTable
set Number = coalesce(number,0) + 1
where ItemCode = '000000' ;
