使用UPDATE SET CASE语句进行T-SQL合并——没有其他语句会发生什么?

I'm writing a TSQL MERGE statement to handle the usual combination of UPDATE and INSERT. I have a couple of select columns which should only be updated if (for example) a parameter is set to TRUE. For example:

我正在编写一个TSQL合并语句来处理更新和插入的常规组合。我有两个选择列，只有当(例如)参数设置为TRUE时才应该更新它们。例如:

...
UPDATE SET
    TARGET.[SomeColumn] =
        CASE
        WHEN @someParameter = 1 THEN SOURCE.[SomeColumn]
    END,
...

According to all documentation I could find, ELSE is definitely optional (as with most languages) but I don't understand what will happen if omitted here.

根据我能找到的所有文档，ELSE绝对是可选的(就像大多数语言一样)，但是我不理解如果省略这里会发生什么。

I could of course do the following:

我当然可以这样做:

...
UPDATE SET
    TARGET.[SomeColumn] =
        CASE
        WHEN @someParameter = 1 THEN SOURCE.[SomeColumn]
        ELSE TARGET.[SomeColumn] --This is the new line
    END,
...

.. but that seems to carry what feels like unnecessary overhead in re-writing existing values.

. .但在重写现有价值时，这似乎带来了不必要的开销。

So my question is what would happen if ELSE was omitted and @someParameter is set to 0 (FALSE), does this fail, set NULL, leave the value unchanged, or...?

所以我的问题是，如果省略了ELSE，将@someParameter设置为0 (FALSE)会发生什么?

2 个解决方案

#1

The facts that it's in an UPDATE in a MERGE are actually irrelevant, although you weren't to know that. The relevant documentation is that for the CASE keyword:

合并中更新的事实实际上是不相关的，尽管你不知道。相关文件是关于CASE关键字的:

ELSE else_result_expression

其他else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL.

如果没有比较操作计算为TRUE，则返回表达式。如果省略此参数，且没有比较操作计算为TRUE，则CASE返回NULL。

#2

No ELSE in a CASE means NULL as per MSDN

在一个CASE中，没有其他的意思是作为MSDN的NULL。

However, MERGE offers another way doing it which will work for multiple columns

但是，MERGE提供了另一种方法，可以用于多个列

WHEN MATCHED AND @someParameter = 1 THEN
    UPDATE SET
        TARGET.[SomeColumn] = SOURCE.[SomeColumn],
        TARGET.[SomeColumn2] = SOURCE.[SomeColumn2]

#1