SQL 将所有 0 值设置为 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4245475/
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
Set all the 0 values to NULL
提问by Marshal
How can I set all the 0 values to NULL in an SQL table ?
如何将 SQL 表中的所有 0 值设置为 NULL？
Thanks in advance.
提前致谢。
回答by NimChimpsky
update table set col = null where col = 0
回答by Aliostad
UPDATE MYTABLE SET MYCOLUMN = NULL WHERE MYCOLUMN = 0
But do you mean in all columns? That will be more work and best you just create separate statements for each column. It is also possible to read the schema and generate an SQL in a stored procedure and EXEC that but I do not recommend since I imagine this is a one-off job and if you are doing this more often then something with the design is wrong.
但你的意思是在所有列中？这将是更多的工作，最好您只需为每列创建单独的语句。也可以在存储过程和 EXEC 中读取模式并生成 SQL，但我不建议这样做，因为我认为这是一次性的工作，如果您经常这样做，那么设计就会出错。
回答by Anthony Faull
UPDATE MyTable
SET Col1 = NULLIF(Col1, 0),
Col2 = NULLIF(Col2, 0),
Col3 = NULLIF(Col3, 0)
WHERE (Col1 = 0
OR Col2 = 0
OR Col3 = 0)
回答by Lieven Keersmaekers
If it is a one time job, you could always grab the output from following statement and execute that.
如果它是一次性工作，您总是可以从以下语句中获取输出并执行它。
SELECT 'UPDATE '
+ OBJECT_NAME(OBJECT_ID)
+ ' SET '
+ sc.name
+ ' = NULL WHERE '
+ sc.name
+ ' = 0'
FROM sys.columns sc
INNER JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE st.name IN ('bigint', 'int', 'smallint')
AND OBJECT_NAME(OBJECT_ID) = 'YourTable'
