为什么 WHEN MATCHED' 不能在 MERGE 语句的 'UPDATE' 子句中出现多次? [英] Why WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement?

问题描述

我正在使用一堆 CRUD 操作并从 CUD 创建合并存储过程.我的存储过程看起来像这样

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

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

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.

我环顾四周并找到了解决此问题的几种方法，但我发现的方法不适用于此错误，因为不是删除，我需要将记录的 IsActive 更新为 0.

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.

此外，在我的搜索中，没有人真正解释为什么会抛出此错误，是的，我知道这很明显，因为错误就在那里，但是为什么不允许发生这种情况?基于这种情况，是否有关于如何实现这一目标的任何想法?或者我应该在 @Action 为 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?

推荐答案

在你的 MERGE 语句中，你有三个 WHEN MATCHED 子句

In your MERGE statement, you have three WHEN MATCHED clauses

• 两个带有 UPDATE 语句
• 带有 INSERT 语句的一个.

• Two with an UPDATE statement
• One with an INSERT statement.

但是，这是不允许的.关于合并的文档:

However, that is not allowed. It is clearly stated in the Documentation on MERGE:

MERGE 语句最多可以有两个 WHEN MATCHED 子句.

The MERGE statement can have at most two WHEN MATCHED clauses.

还有

如果有两个 WHEN MATCHED 子句，那么一个必须指定一个 UPDATE 操作，一个必须指定一个 DELETE 操作.

If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

同样重要的是:

如果 UPDATE 在 <merge_matched> 中指定子句，以及多于一行的<table_source>根据 <merge_search_condition> 匹配 target_table 中的一行，SQL Server 返回错误.MERGE 语句不能多次更新同一行，也不能更新和删除同一行.

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.

这篇关于为什么 WHEN MATCHED' 不能在 MERGE 语句的 'UPDATE' 子句中出现多次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！