如果值不为空，则用于更新数据库的 Sql 查询?

2022-01-17 00:00:00 sql-update sql database sqlite

我有一个包含大约 17 个字段的表.我需要在这个表中执行频繁的更新.但问题是每次我可能只更新几个字段.在这种情况下编写更新查询的最佳方法是什么?我正在寻找一个选项，其中 值只有在它不为空时才会更新.

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.

例如，我在数据库中有四个字段说 A、B、C、D.用户更新说 D 的值.所有其他值保持不变.所以我想要一个更新查询，它只更新 D 的值，保持其他不变.因此，如果我将 a、b 和 c 设为 null 并将 d 设为用户提供的值，我想编写一个更新查询，该查询仅将 d 的值更新为 a、b 且 c 为 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?

我正在使用 SQLite 数据库.

I am using SQLite database.

有人可以给它一些亮点吗?

Could someone please throw some light into it?

推荐答案

在不了解您的数据库的情况下，很难具体说明.在 SQL Server 中，语法类似于 ...

Without knowing your database it's tough to be specific. In SQL Server the syntax would be something like ...

UPDATE MyTable 
SET 
        Field1 = IsNull(@Field1, Field1),
        Field2 = IsNull(@Field2, Field2),
        Field3 = IsNull(@Field3, Field3)
WHERE 
     <your criteria here>

编辑

由于您指定了 SQLLite ...将我的 IsNull 函数替换为 COALESCE() 或查看 IfNull 函数.

Since you specified SQLLite ...replace my IsNull function with COALESCE() or alternately look at the IfNull function.

相关文章