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

i'm taking bunch of crud opertations , creating merge storedprocs off cud. stored proc looks this

create procedure usp_administrationhistorymerge     @administrationhistoryid int out,     @administratorid int,     @datecreated datetime,     @createdby nvarchar(50),     @modifiedby nvarchar(50),     @action int  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] target     using (select @administratorid, @datecreated, @createdby, @datemodified, @modifiedby, @isactive)     source (administratorid, datecreated, createdby, datemodified, modifiedby, isactive)     on (target.administrationhistoryid = source.administrationhistoryid)     when matched , @action = -1         update             set isactive = 0     when matched , @action = 0         update         set modifiedby = @modifiedby,         datemodified = getdate()     when matched , @action = 1     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 transaction uncommittable.     if (xact_state()) = -1         begin             --print n'the transaction in uncommittable state. rolling transaction.'             rollback transaction;         end;      -- test if transaction active , valid.     if (xact_state()) = 1         begin             --print n'the transaction committable. committing transaction.'             commit transaction;            end;      set @message = 'error occured in stored procedure ' + cast(@error_procedure varchar(200)) +                      '; line number ' + cast(@error_line varchar) +                      '; message: [' + cast(@error_number varchar) + '] - '                     + cast(@error_message varchar(255))      raiserror(@message, @error_severity, 1); end catch; 

when go execute getting full error

msg 10714, level 15, state 1, procedure usp_administrationhistorymerge, line 36 action of type 'when matched' cannot appear more once in 'update' clause of merge statement.

i have looked around on , found couple ways resolve this, have found aren't suitable solutions error, instead of delete , need update record's isactive 0.

also, in searching no 1 explains why error being thrown, yes know obvious because error right there, why not allowed happen? , based on circumstance there idea's on how accomplish this? or should have merge call storedproc when @action 0?

in merge statement, have 3 when matched clauses

• two update statement
• one insert statement.

however, not allowed. stated in documentation on merge:

the merge statement can have @ 2 when matched clauses.

and

if there 2 when matched clauses, 1 must specify update action , 1 must specify delete action.

also important know is:

if update specified in <merge_matched> clause, , more 1 row of <table_source> matches row in target_table based on <merge_search_condition>, sql server returns error. merge statement cannot update same row more once, or update , delete same row.