对于更新查询，将空字段视为零 [英] Treating null field as zero for an update query

问题描述

我正在使用SQL Express 2010查询构建器。我需要能够增加一个字段。

I'm using the SQL Express 2010 query builder. I need to be able to increment a field.

在我的背后代码中，我打电话给

In my behind code, I make a call such as

tableAdapter.IncrementLikeCount(id);

如果我仅使用增量，则like字段可以为null，因此我想选择
a。在该字段中将null视为零或
b。

If I just use an increment, the like field can be null, so I want to either a. treat the null as zero in that field OR b. set to 1, if null, and increment otherwise.

我最近尝试过的是在查询生成器中使用以下代码的选项b：

The most current thing I tried is option b with the following code in the query builder:

UPDATE       [dbo].[myTable]
SET                [LikeCount] = IIF(ISNULL([LikeCount]), 1, LikeCount + 1)
WHERE        ([ID] = @Original_ID)

但是，这不起作用。查询生成器将不断重写ISNULL内的表达式，而无需使用方括号和逗号，如下所示：

However, this does not work. The query builder keeps rewriting the expression inside the ISNULL without the square brackets and with a comma, as the following:

UPDATE       [dbo].[myTable]
SET                [LikeCount] = IIF(ISNULL(LikeCount,), 1, LikeCount + 1)
WHERE        ([ID] = @Original_ID)

是否有一种干净，简单的方法？

Is there a clean, simple way of doing this?

推荐答案

ISNULL语句需要使用默认值，例如

The ISNULL statement needs a default to fall back to, like

ISNULL(LikeCount, 0)

其中0是LikeCount变成IF的值，实际上它为空。

where the 0 is the value that LikeCount becomes IF in fact it is null.

因此，尝试

UPDATE       [dbo].[myTable]
SET          [LikeCount] = (ISNULL(LikeCount, 0) + 1)
WHERE        ([ID] = @Original_ID)

更新

关于您在评论中发布的查询：

As to the query you posted in your comment:

UPDATE Documents 
SET docLikeCount = ISNULL(docLikeCount, 0) + 1
WHERE docID = @Original_docID

这篇关于对于更新查询，将空字段视为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！