Why can't WHEN MATCHED 'appear more than once in an UPDATE clause in a MERGE statement?

Question

I take a bunch of CRUD operations and create a memory merge with CUD. My stored proc is as follows

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 accomplish this, I get this complete error

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

I looked through SO and found a couple of ways to solve this problem, but what I found is not suitable for this error, but instead of deleting, and I need to update the IsActive entry to 0.

Also, in my search no one really explains why this error occurs, yes, I know its obvious, because the error is right there, but why is it not allowed? and based on this circumstance, is there any idea on how to do this? or do I need this merge to call another storedproc when @Action is 0?

Answer 1

In your MERGE , you have three WHEN MATCHED

• Two with an UPDATE
• One with an INSERT .

However, this is unacceptable. This is clearly stated in the MERGE Documentation :

A MERGE can have no more than two WHEN MATCHED .

AND

If there are two WHEN MATCHED , then the UPDATE action must be specified, and the DELETE action must be specified.

It is also important to know:

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