问为什么匹配的“不能在合并语句的”UPDATE“子句中出现不止一次？
EN

Stack Overflow用户

提问于 2016-01-24 07:31:19

回答 2查看 6.9K关注 0票数 9

我把一堆CRUD操作从CUD中删除并创建合并存储库。我存储的proc如下所示

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;

当我去执行它时，我会得到这个完整的错误。

Msg 10714、级别15、状态1、过程usp_AdministrationHistoryMerge、第36行类型的动作“匹配时”不能在合并语句的“UPDATE”子句中多次出现。

我已经查看过SO，并找到了几种解决这个问题的方法，但是我找到的不是针对这个错误的合适的解决方案，而是删除，我需要将记录的IsActive更新为0。

而且，在我的搜索中，没有人真正解释为什么要抛出这个错误，是的，我知道这很明显，因为错误就在那里，但是为什么不允许这种情况发生呢？基于这种情况，对于如何做到这一点，有什么想法吗？或者，当@Action为0时，是否应该让这个合并调用另一个storedproc？

sql

sql-server

merge

大数据产品特惠专场

BI、WeData新客仅9.9元！新客首单1折起！

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-01-24 13:43:02

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

两个带有一个UPDATE语句
一个带有INSERT语句。

然而，这是不允许的。关于合并的文档明确指出：

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

和

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

同样重要的是要知道：

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

票数 9

Stack Overflow用户

发布于 2020-04-10 01:00:42

SQL标准(例如Db2)允许这样做，但不允许Server。SQL Server中的子句。在你的情况下

MERGE INTO [AdministrationHistory] AS target
USING (
  SELECT @AdministratorID, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy, @IsActive
) AS source (AdministratorID, DateCreated, CreatedBy, DateModified, ModifiedBy, IsActive)
ON (target.AdministrationHistoryID = source.AdministrationHistoryID)

-- Combine both predicates using OR. This is optional.
WHEN MATCHED AND @Action = -1 OR @Action = 0 THEN UPDATE SET

  -- UPDATE clause from the first "WHEN MATCHED AND @Action = -1" clause
  IsActive = CASE WHEN @Action = -1 THEN 0 ELSE IsActive END,

  -- UPDATE clauses from the second "WHEN MATCHED AND @Action = 0" clause
  ModifiedBy = CASE WHEN @Action = 0 THEN @ModifiedBy ELSE ModifiedBy END,
  DateModified = CASE WHEN @Action = 0 THEN GETDATE() ELSE DateModified END

WHEN NOT MATCHED AND @Action = 1 THEN INSERT
    (AdministratorID, DateCreated, CreatedBy, IsActive)
  VALUES 
    (@AdministratorID, @DateCreated, @CreatedBy, @IsActive);