Why When Matched' Cannot Appear More Than Once in a 'Update' Clause of a Merge Statement

Why WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement?

In your MERGE statement, you have three WHEN MATCHED clauses

• Two with an UPDATE statement
• One with an INSERT statement.

However, that is not allowed. It is clearly stated in the Documentation on MERGE:

The MERGE statement can have at most two WHEN MATCHED clauses.

And

If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

Also important to know is:

If UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. The MERGE statement cannot update the same row more than once, or update and delete the same row.

An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement

Use CASE expressions in your update based on the condition you currently have as part of your 'MATCHED' branch. i.e. for the ones you only update in one branch use the case expression to either update it, or leave it the same. Here is an example of one of your columns.

[Target].[LinkyCode] =
    CASE
    WHEN CONVERT(DATETIME, [Source].[LinkyPiTRunDateUTC]) <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyCode]
    ELSE [TARGET].[LinkyCode]
    END

MERGE statement with two WHEN MATCHED

The general case can be emulated using CASE expressions as I've shown in this answer here, or in this blog post here. But your case is more specific, because the difference between the two clauses is simpler than the general case. You can combine them into one clause:

WHEN MATCHED THEN UPDATE SET
  -- These are always updated the same way, regardless of the WHEN MATCHED AND predicate
  date_time_updated = GETDATE(),
  user_no_updated = @user_no,

  -- These depend on the relevant WHEN MATCHED AND predicate, so use CASE
  stock_warehouse = CASE 
    WHEN T._stock_warehouse IS NOT NULL THEN T._stock_warehouse 
    ELSE stock_warehouse
  END,
  archive = CASE WHEN T._stock_warehouse IS NULL THEN 1 ELSE archive END

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

IF EXISTS and MERGE Statement

Seems you have duplicate rows in your target table which are loaded from your previous runs.

Note: Matching in a Merge does not consider the rows inserted (even duplicate) while running the Merge itself.

Below is my repro example with a sample data:

Table1: Initial data

Table2: Taget table

Merge Statement:

MERGE tb2 AS Target
USING tb1   AS Source
 ON Source.firstname = Target.firstname and 
    Source.lastname = Target.lastname 

-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date) 
VALUES (Source.firstname, Source.lastname, source.updated_date)

-- For Updates
WHEN MATCHED THEN UPDATE SET
   Target.updated_date      = Source.updated_date

-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;

When Merge is executed, it inserts all data without any errors.

New data in tb1:

When I run the Merge statement, it gives me the same error as yours.

As a workaround using one of the below options,

1. Add additional conditions if possible in the ON clause to uniquely identify the data.

2. Remove the duplicates from the source and merge the data into tb2 as below.

    --temp table
    drop table if exists #tb1;
   
    select *  into #tb1 from (
        select *, row_number() over(partition by firstname, lastname order by firstname, lastname, updated_date desc) as rn from tb1) a
    where rn = 1 
   
    MERGE tb2 AS Target
    USING #tb1 AS Source
     ON Source.firstname = Target.firstname and 
        Source.lastname = Target.lastname 
   
    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
    INSERT (firstname, lastname, updated_date) 
      VALUES (Source.firstname, Source.lastname, source.updated_date)
   
    -- For Updates
    WHEN MATCHED THEN UPDATE SET
       Target.updated_date     = Source.updated_date
   
    -- For Deletes
    WHEN NOT MATCHED BY Source THEN
      DELETE;
   

Data merged into tb2 successfully.

SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE

You are joining the tables on ON source.MappingId = target.MappingId.

In your data sample, there are more than 1 row with same MappingId = 185761.
So here you got:

A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.

You need to specify some unique column combination to join the source and the target tables.

---

Related Topics