When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?

Question

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

My example:

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;

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

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

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

Answer 1

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 expression is rather useful for this.

Something like this as an example:

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

---

And the results:

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