Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 680
Codeman Avatar asked May 30 '13 23:05

Codeman


People also ask

How many when matched clause can be used in a MERGE statement?

The MERGE statement can have, at most, two WHEN MATCHED clauses. If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first isn't.

How does MERGE statement work in SQL?

The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, or delete, etc.

Which of the following is performed by MERGE statement?

The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement, eliminating the need to write separate logic for each. It changes the data in a target table based on the data in a source table.


2 Answers

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.

like image 100
Codeman Avatar answered Sep 28 '22 00:09

Codeman


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 
like image 27
sgeddes Avatar answered Sep 27 '22 22:09

sgeddes