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

by

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.

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

Leave a Comment

tech