为什么在MERGE语句的“ UPDATE”子句中，“ WHEN MATCHED”不能多次出现？

Question

我正在进行大量CRUD操作，并在CUD之外创建合并存储过程。我存储的过程看起来像这样

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;

当我执行此操作时，我得到了这个完整的错误

消息10714，级别15，状态1，过程usp_AdministrationHistoryMerge，第36行在MERGE语句的“
UPDATE”子句中，“ WHEN MATCHED”类型的动作不能出现多次。

我四处寻找SO，并找到了解决此问题的几种方法，但是我发现的解决方案并不适合此错误，因为不是删除，而是需要将记录的IsActive更新为0。

另外，在我的搜索中，没有人能真正解释为什么会引发此错误，是的，我知道它很明显，因为错误就在那里，但是为什么不允许这种错误发生呢？在这种情况下，关于如何实现这一点有什么想法吗？还是应该在@Act​​ion为0时让此合并调用另一个storedproc？

Answer 1

在您的MERGE陈述中，您有三个WHEN MATCHED子句

• 两个有一个UPDATE声明
• 一个INSERT声明。

但是，这是不允许的。在MERGE的文档中明确指出：

该MERGE语句最多可以包含两个WHEN MATCHED子句。

和

如果有两个WHEN MATCHED子句，则一个子句必须指定一个UPDATE动作，另一个子句必须指定一个DELETE动作。

同样重要的是要知道：

如果UPDATE在 子句中指定，并且 的多行与基于 的target_table中的一行匹配，则SQL
Server返回错误。该 MERGE语句不能多次更新同一行，也不能更新和删除同一行。