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

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

My example:

  • Creating a struct type as a column value for T-SQL?
  • Why can't I use “create schema” in a begin/end block in SQL Management Studio?
  • How can I delete duplicate rows in a table
  • Microsoft SQL Server - restricting subqueries
  • conditional unique constraint
  • SQL Query to find earliest date dependent on column value changing
  • 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.

  • INSERT lots of records using SQL Server 2008
  • SQLXML without XML encoding?
  • INSERT XML into SQL Server 2008 database
  • How to get result from parent child table
  • RoundhousE - Change login used by scripts scripts
  • for each in MS SQL SERVER?
  • 3 Solutions collect form web for “When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?”

    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
    

    I found in the MSDN documentation:

    WHEN MATCHED THEN

    Specifies that all rows of target_table that match the rows returned by ON , and satisfy any additional search condition, are either updated or deleted according to the clause.

    The MERGE statement can have at most two WHEN MATCHED clauses. If two clauses are specified, then the first clause must be accompanied by an AND clause. For any given row, the second WHEN MATCHED clause is only applied if the first is not. If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action. If UPDATE is specified in the clause, and more than one row of matches a row in target_table based on , SQL Server returns an error. The MERGE statement cannot update the same row more than once, or update and delete the same row.

    So it looks like only one of the statements are executed, and they require a DELETE in one and an UPDATE in the other.

    well the answer is, do you really want to because if you do you would change a set based update to a row by agonising slow row update as in a set of rows you really would not know what columns changed on a record by record basis.

    the question therefore is do you want to get performance? if so, make sure you have indexes that covering the

    WHEN MATCHED TARGET.FIELD1 = SOURCE:FIELD1 AND TARGET.FIELD2 = SOURCE:FIELD2 …

    if not you are going to have to cursor over your updates after the merge using an INSTEAD OF trigger…

    Not good for speed, however can work if you need to record who did what…

    Happy Coding

    Walter

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.