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

Question

I'm taking a bunch of CRUD opertations and creating merge storedprocs off the CUD. My stored proc looks like this

CREATE PROCEDURE usp_AdministrationHistoryMerge
    @AdministrationHistoryID int out,
    @AdministratorID int,
    @DateCreated datetime,
    @CreatedBy nvarchar(50),
    @ModifiedBy nvarchar(50),
    @Action int
AS

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @ERROR_SEVERITY int,
        @MESSAGE varchar(1000),
        @ERROR_NUMBER int,
        @ERROR_PROCEDURE nvarchar(200),
        @ERROR_LINE int,
        @ERROR_MESSAGE nvarchar(4000),
        @IsActive bit,
        @DateModified datetime;
begin try
    if @Action = 1
        begin
            set @IsActive = 1
            set @AdministrationHistoryID = SCOPE_IDENTITY()
        end
    merge [AdministrationHistory] as target
    using (select @AdministratorID, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy, @IsActive)
    as source (AdministratorID, DateCreated, CreatedBy, DateModified, ModifiedBy, IsActive)
    on (target.AdministrationHistoryID = source.AdministrationHistoryID)
    when matched and @Action = -1 then
        update
            set IsActive = 0
    when matched and @Action = 0 then
        update
        set ModifiedBy = @ModifiedBy,
        DateModified = GETDATE()
    when matched and @Action = 1 then
    insert
    (AdministratorID, DateCreated, CreatedBy, IsActive)
    values
    (@AdministratorID, @DateCreated, @CreatedBy, @IsActive);
end try

BEGIN CATCH
    SET @ERROR_SEVERITY = ISNULL(ERROR_SEVERITY(),'');
    SET @ERROR_NUMBER = ISNULL(ERROR_NUMBER(),'');
    SET @ERROR_PROCEDURE = ISNULL(ERROR_PROCEDURE(),''); 
    SET @ERROR_LINE = ISNULL(ERROR_LINE(),'');
    SET @ERROR_MESSAGE = ISNULL(ERROR_MESSAGE(),'');

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
        BEGIN
            --PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;

    -- Test if the transaction is active and valid.
    IF (XACT_STATE()) = 1
        BEGIN
            --PRINT N'The transaction is committable. Committing transaction.'
            COMMIT TRANSACTION;   
        END;

    SET @MESSAGE = 'Error Occured in Stored Procedure ' + cast(@ERROR_PROCEDURE as varchar(200)) + 
                    '; Line Number ' + cast(@ERROR_LINE as varchar) + 
                    '; Message: [' + cast(@ERROR_NUMBER as varchar) + '] - '
                    + cast(@ERROR_MESSAGE as varchar(255))

    RAISERROR(@MESSAGE, @ERROR_SEVERITY, 1);
END CATCH;

When I go to execute this I am getting this full error

Msg 10714, Level 15, State 1, Procedure usp_AdministrationHistoryMerge, Line 36 An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

I have looked around on SO and found a couple ways to resolve this, but what I have found aren't suitable solutions for this error, as instead of a delete and I need to update the record's IsActive to a 0.

Also, in my searching no one really explains why this error is being thrown, yes I know its obvious because the error is right there, but why is this not allowed to happen? and based on this circumstance are there any idea's on how to accomplish this? or should I have this merge call another storedproc when @Action is 0?

Answer 1

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.