本文介绍了当使用多个WHEN MATCHED语句时,它们都执行,还是只执行一个?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我在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 Demo
结果:
A B C
--------------
0 0 (null)
0 1 1
1 0 0
1 1 0
这篇关于当使用多个WHEN MATCHED语句时,它们都执行,还是只执行一个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!