CODEWITHSUNDEEP
sql-server-2008merge
Arthur Rey
Arthur Rey

Reputation: 3058

MERGE statement with two WHEN MATCHED

I have this MERGE statement working fine:

MERGE INTO product_replenishment PR
USING ( SELECT * FROM traxs_temp..__MinMaxImport WHERE session_id = @session_id ) T
    ON T._product = PR.product AND T._branch_no = PR.branch_no
WHEN MATCHED AND T._stock_warehouse IS NOT NULL THEN
    UPDATE SET
        date_time_updated = GETDATE(),
        user_no_updated = @user_no,
        stock_warehouse = T._stock_warehouse
WHEN NOT MATCHED BY TARGET AND T._stock_warehouse IS NOT NULL THEN
    INSERT 
        (date_time_created,
        date_time_updated,
        user_no_created,
        user_no_updated,
        branch_no,
        product,
        stock_warehouse,
        archive) 
    VALUES 
        (GETDATE(),
        GETDATE(),
        @user_no,
        @user_no,
        T._branch_no,
        T._product,
        T._stock_warehouse,
        0);

I want to add another WHEN MATCHED statement like this:

WHEN MATCHED AND T._stock_warehouse IS NOT NULL THEN
    UPDATE SET
        date_time_updated = GETDATE(),
        user_no_updated = @user_no,
        stock_warehouse = T._stock_warehouse
WHEN MATCHED AND T._stock_warehouse IS NULL THEN
    UPDATE SET
        date_time_updated = GETDATE(),
        user_no_updated = @user_no,
        archive = 1

But I get an error: An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

Is it impossible to achieve what I'm trying to do?

Upvotes: 1

Views: 246

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220797

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

Upvotes: 1

Related Questions