SQL 如何将所有记录的空白（null）值替换为 0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1977133/
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
How to replace blank (null ) values with 0 for all records?
提问by rick
MS Access: How to replace blank (null ) values with 0 for all records?
MS Access：如何将所有记录的空白（空）值替换为 0？
I guess it has to be done using SQL. I can use Find and Replace to replace 0 with blank, but not the other way around (won't "find" a blank, even if I enter [Ctrl-Spacebar] which inserts a space.
我想它必须使用 SQL 来完成。我可以使用查找和替换将 0 替换为空白，但不能反过来（即使我输入插入空格的 [Ctrl-Spacebar]，也不会“找到”空白。
So I guess I need to do SQL where I find null values for MyField, then replace all of them with 0.
所以我想我需要执行 SQL，在其中找到 MyField 的空值，然后将它们全部替换为 0。
回答by Charles Bretana
Go to the query designer window, switch to SQL mode, and try this:
转到查询设计器窗口，切换到 SQL 模式，然后尝试以下操作：
Update Table Set MyField = 0
Where MyField Is Null;
回答by Gabriel McAdams
If you're trying to do this with a query, then here is your answer:
如果您尝试通过查询执行此操作，那么这里是您的答案：
SELECT ISNULL([field], 0) FROM [table]
SELECT ISNULL([field], 0) FROM [表]
Edit
编辑
ISNULL function was used incorrectly - this modified version uses IIF
ISNULL 函数使用不正确 - 此修改版本使用 IIF
SELECT IIF(ISNULL([field]), 0, [field]) FROM [table]
If you want to replace the actual values in the table, then you'll need to do it this way:
如果要替换表中的实际值，则需要这样做：
UPDATE [table] SET [FIELD] = 0 WHERE [FIELD] IS NULL
回答by Philippe Grondier
without 'where's and 'if's ...
没有'where's 和'if's ...
Update Table Set MyField = Nz(MyField,0)
回答by user3292421
Better solution is to use NZ (null to zero) function during generating table => NZ([ColumnName]) It comes 0 where is "null" in ColumnName.
更好的解决方案是在生成表时使用 NZ (null to zero) 函数 => NZ([ColumnName]) 它是 0，其中 ColumnName 为“null”。
回答by Ken White
UPDATE YourTable SET MyField = 0 WHERE MyField IS NULL
works in most SQL dialects. I don't use Access, but that should get you started.
适用于大多数 SQL 方言。我不使用 Access，但这应该可以帮助您入门。
回答by mopoke
UPDATE table SET column=0 WHERE column IS NULL
回答by Sadie
The following Query also works and you won't need an update query if that's what you'd prefer:
以下查询也有效，如果您愿意，则不需要更新查询：
IIF(Column Is Null,0,Column)
回答by Jim Keifer
Using find and replace will work if you type "null" in the find and put a zero in the replace...you will be warned that this cannot be undone.
如果您在查找中键入“null”并在替换中输入零，则使用查找和替换将起作用……您将被警告这是无法撤消的。
回答by Aly
I just had this same problem, and I ended up finding the simplest solution which works for my needs. In the table properties, I set the default value to 0 for the fields that I don't want to show nulls. Super easy.
我刚刚遇到了同样的问题，我最终找到了最适合我需求的最简单的解决方案。在表属性中，我将不想显示空值的字段的默认值设置为 0。超级容易。
回答by Guest1
I used a two step process to change rows with "blank" values to "Null" values as place holders.
我使用两步过程将具有“空白”值的行更改为“空”值作为占位符。
UPDATE [TableName] SET [TableName].[ColumnName] = "0"
WHERE ((([TableName].[ColumnName])=""));
UPDATE [TableName] SET [TableName].[ColumnName] = "Null"
WHERE ((([TableName].[ColumnName])="0"));
