本文介绍了当使用多个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语句时,它们都执行,还是只执行一个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-23 14:34