如果值不为空，用于更新数据库的 Sql 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6099570/
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
Sql query for updating database if value is not null?
提问by Zach
I am having a table which has about 17 fields. I need to perform frequent updates in this table. But the issue is each time I may be updating only a few fields. Whats the best way to write a query for updating in such a scenario? I am looking for an option in which the value gets updated only if it is not null.
我有一个大约有 17 个字段的表。我需要在这个表中执行频繁的更新。但问题是每次我可能只更新几个字段。在这种情况下编写更新查询的最佳方法是什么？我正在寻找一个选项，其中的值只有在它不为 null 时才会更新。
For example I have four fields in database Say A,B,C,D. User updates the value of say D. All other values remains the same. So I want an update query which updates only the value of D keeping the others unaltered. SO if i put a,b and c as null and d with the value supplied by user I want to write an update query which only updates the value of d as a,b and c is null. Is it something achievable?
例如，我在数据库 Say A、B、C、D 中有四个字段。用户更新 say D 的值。所有其他值保持不变。所以我想要一个更新查询，它只更新 D 的值，保持其他值不变。因此，如果我将 a、b 和 c 设置为 null，将 d 设置为用户提供的值，我想编写一个更新查询，该查询仅将 d 的值更新为 a、b 和 c 为空。它是可以实现的吗？
I am using SQLite database.
我正在使用 SQLite 数据库。
Could someone please throw some light into it?
有人可以给它一些光吗？
回答by EBarr
Without knowing your database it's tough to be specific. In SQL Server the syntax would be something like ...
在不知道您的数据库的情况下，很难具体说明。在 SQL Server 中，语法类似于...
UPDATE MyTable
SET
Field1 = IsNull(@Field1, Field1),
Field2 = IsNull(@Field2, Field2),
Field3 = IsNull(@Field3, Field3)
WHERE
<your criteria here>
EDIT
编辑
Since you specified SQLLite ...replace my IsNullfunction with COALESCE()or alternately look at the IfNullfunction.
由于您指定了 SQLLite ...用 该函数替换我的IsNull函数COALESCE()或交替查看该IfNull函数。
回答by OverworkedTechydude
Posting a SQL Server solution with 2 tables for posterity. Query joins two tables and updates the values that are present. Otherwise original value is maintained.
为后代发布带有 2 个表的 SQL Server 解决方案。查询连接两个表并更新存在的值。否则保留原始值。
tables = table1, table2 each having field1 and field2
表 = 表 1，表 2 每个都有字段 1 和字段 2
update t1 WITH (ROWLOCK)
set T1.Field2 = ISNULL(T2.Field2,T1.Field2)
from Table1 T1 Join Table2 T2
ON T1.Field1 = T2.Field1
