当使用多个WHEN MATCHED语句时，它们都执行，还是只执行一个？ [英] When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?

问题描述

如果我在MERGE语句中有多个WHEN MATCHED语句，那么如果它们是真的，它们是否都执行？

If I have multiple WHEN MATCHED statements in a MERGE statement, do they all execute if they're true?

我的示例：

DECLARE @X bit = NULL;

--skipping the MERGE statement, straight to WHEN MATCHED

WHEN MATCHED AND A = 1
    @X = 0;
WHEN MATCHED AND B = 1
    @X = 1;

在这四种可能性中，X的状态是什么？

What is the state of X in each of the 4 possibilities?

A|B|X
0|0|?
0|1|?
1|0|?
1|1|?

基本上，我很好奇，如果每个WHEN MATCHED子句后有一个隐式BREAK。

Basically, I'm curious if there's an implicit BREAK after each WHEN MATCHED clause.

推荐答案

要回答你的问题，是的，它只会运行一个匹配，然后打破。但是，如果你想有逻辑允许条件匹配在更新， CASE 语句是非常有用的。

To answer your question, yes, it will only run a single match and then break. However, if you'd like to have logic to allow for conditional matching in the update, the CASE statement is rather useful for this.

这样的例子：

MERGE INTO YourTable
USING (VALUES (1, 1, NULL), (0, 0, NULL), (0, 1, NULL), (1, 0, NULL))
       T2 (a2,b2,c2)
ON a = a2 AND b = b2
WHEN MATCHED  THEN
    UPDATE SET c = 
      CASE 
        WHEN a = 1 THEN 0
        WHEN b = 1 THEN 1
        ELSE NULL
      END        
WHEN NOT MATCHED THEN
    INSERT (a, b) VALUES (a2, b2);

SELECT * FROM YourTable ORDER BY a,b;

• SQL Fiddle演示


• SQL Fiddle Demo

结果：

A   B   C
--------------
0   0   (null)
0   1   1
1   0   0
1   1   0

这篇关于当使用多个WHEN MATCHED语句时，它们都执行，还是只执行一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！