SQL MERGE - 多个 WHEN MATCHED 案例与更新

MERGE - Multiple WHEN MATCHED cases with update

提问by Seb

When I launch this request, I receive the SQL Server number error 10714. I understand that I can't use more than one UPDATE with WHEN MATHED but I don't know how can I do.

当我启动此请求时，我收到 SQL Server 编号错误 10714。我知道我不能使用 WHEN MATHED 的多个更新，但我不知道我该怎么做。

MERGE INTO Photo p
USING TmpPhoto tp
ON p.ProductNumberID = tp.ProductNumberID and p.SHA1 = tp.SHA1
WHEN MATCHED AND p.Rank = tp.Rank THEN
    UPDATE SET p.VerifiedDate = getDate()
WHEN MATCHED AND p.Rank != tp.Rank AND tp.Rank != 1 THEN
    UPDATE SET p.VerifiedDate = getDate(), p.Rank = tp.Rank, p.Active = 0
WHEN MATCHED AND p.Rank != tp.Rank AND tp.Rank = 1 THEN
    UPDATE SET p.VerifiedDate = getDate(), p.Rank = tp.Rank, p.Active = 1
WHEN NOT MATCHED THEN
    INSERT (ProductNumberID, Code, Extension, Rank, CreatedDate, VerifiedDate, FCTH, SHA1, Active)
    VALUES (tp.ProductNumberID, tp.Code, tp.Extension, tp.Rank, getdate(), getdate(), tp.FCTH, tp.SHA1, 0)
    OUTPUT inserted.NumberID as PhotoNumberID, inserted.ProductNumberID, inserted.SHA1, inserted.Rank INTO InsertedPhotos;

采纳答案by Alexandru Chichinete

Simplified version (verifiedDate is updated always, rank is updated always since if it's equal it stays the same, the only field that change is p.Active using CASE)

简化版本（verifiedDate 始终更新，排名始终更新，因为如果相等则保持不变，唯一更改的字段是 p.Active using CASE）

MERGE INTO Photo p
USING TmpPhoto tp
    ON p.ProductNumberID = tp.ProductNumberID and p.SHA1 = tp.SHA1
    WHEN MATCHED 
     THEN
       UPDATE SET 
           p.VerifiedDate = getDate(),
           p.RANK = tp.RANK,
           p.Active = 
              (CASE
                WHEN p.Rank != tp.Rank AND tp.Rank != 1 THEN 0
                WHEN p.Rank != tp.Rank AND tp.Rank = 1 THEN 1
                ELSE p.Active END
             )
    WHEN NOT MATCHED THEN
        INSERT (ProductNumberID, Code, Extension, Rank, CreatedDate, VerifiedDate, FCTH, SHA1, Active)
        VALUES (tp.ProductNumberID, tp.Code, tp.Extension, tp.Rank, getdate(), getdate(), tp.FCTH, tp.SHA1, 0)
        OUTPUT inserted.NumberID as PhotoNumberID, inserted.ProductNumberID, inserted.SHA1, inserted.Rank INTO InsertedPhotos;

回答by siride

If you can, use CASEexpressions in your UPDATEsub-statements to mimic the behavior of having multiple WHEN MATCHEDclauses. Something like this:

如果可以，请CASE在UPDATE子语句中使用表达式来模拟具有多个WHEN MATCHED子句的行为。像这样的东西：

MERGE INTO Photo p
USING TmpPhoto tp
ON p.ProductNumberID = tp.ProductNumberID and p.SHA1 = tp.SHA1
WHEN MATCHED THEN
    UPDATE 
       SET p.VerifiedDate = getDate(),
           p.Rank = CASE 
                        WHEN p.Rank != tp.Rank AND tp.Rank != 1 THEN tp.Rank                       
                        ELSE p.Rank
                    END,
           p.Active = CASE
                          WHEN p.Rank = tp.Rank THEN p.Active 
                          WHEN tp.Rank != 1 THEN 0
                          ELSE 1
                      END
WHEN NOT MATCHED THEN
    INSERT (ProductNumberID, Code, Extension, Rank, CreatedDate, VerifiedDate, FCTH, SHA1, Active)
    VALUES (tp.ProductNumberID, tp.Code, tp.Extension, tp.Rank, getdate(), getdate(), tp.FCTH, tp.SHA1, 0)
    OUTPUT inserted.NumberID as PhotoNumberID, inserted.ProductNumberID, inserted.SHA1, inserted.Rank INTO InsertedPhotos;

What this does is move the logic about which fields to update and how into CASEexpressions. Note that if a field isn't to be updated, then it is simply set to itself. In SQL Server, this appears to be a no-op. However, I'm not sure if it will count as a modified column for triggers. You can always test to see if the row actually changed in the trigger to avoid any problems this approach might cause.

这样做是将有关要更新哪些字段以及如何更新的逻辑移动到CASE表达式中。请注意，如果不更新字段，则只需将其设置为自身。在 SQL Server 中，这似乎是一个空操作。但是，我不确定它是否会算作触发器的修改列。您始终可以测试以查看触发器中的行是否实际更改，以避免此方法可能导致的任何问题。

回答by Evaldas Buinauskas

Have you considered using CASEstatement when doing an update?

您是否考虑过在进行更新时使用CASE语句？

There might be a syntax issue somewhere here. Let me know if this works.

这里的某个地方可能存在语法问题。让我知道这个是否奏效。

MERGE INTO Photo p
USING TmpPhoto tp
    ON p.ProductNumberID = tp.ProductNumberID
    AND p.SHA1 = tp.SHA1
WHEN MATCHED THEN
    UPDATE SET p.VerifiedDate = GETDATE()
        , p.Rank = CASE
            WHEN p.Rank != tp.Rank THEN tp.Rank
            ELSE p.Rank
        END
        , p.Active = CASE
            WHEN p.Rank != tp.Rank AND tp.Rank != 1 THEN 0
            WHEN p.Rank != tp.Rank AND tp.Rank = 1 THEN 1
            ELSE p.Active
        END
WHEN NOT MATCHED THEN
    INSERT (ProductNumberID, Code, Extension, Rank, CreatedDate, VerifiedDate, FCTH, SHA1, Active)
    VALUES (tp.ProductNumberID, tp.Code, tp.Extension, tp.Rank, getdate(), getdate(), tp.FCTH, tp.SHA1, 0)
    OUTPUT inserted.NumberID as PhotoNumberID, inserted.ProductNumberID, inserted.SHA1, inserted.Rank INTO InsertedPhotos;

回答by Ragul

Why don't you try using CASEstatement,

为什么不尝试使用CASE语句，

MERGE INTO 
      Photo p
USING 
      TmpPhoto tp  ON p.ProductNumberID = tp.ProductNumberID 
                   AND p.SHA1 = tp.SHA1
WHEN 
    MATCHED AND p.Rank = tp.Rank 
THEN
    UPDATE 
        SET    p.VerifiedDate = GETDATE(),
               p.Rank = CASE 
                            WHEN p.Rank != tp.Rank AND tp.Rank != 1 THEN tp.Rank
                            WHEN p.Rank != tp.Rank AND tp.Rank  = 1 THEN tp.Rank
                            ELSE p.Rank END,
               p.Active = CASE 
                            WHEN p.Rank != tp.Rank AND tp.Rank != 1 THEN 0
                            WHEN p.Rank != tp.Rank AND tp.Rank  = 1 THEN 1
                            ELSE p.Active END
WHEN 
    NOT MATCHED
THEN
    INSERT (ProductNumberID, Code, Extension, Rank, CreatedDate, VerifiedDate, FCTH, SHA1, Active)
    VALUES (tp.ProductNumberID, tp.Code, tp.Extension, tp.Rank, getdate(), getdate(), tp.FCTH, tp.SHA1, 0)
    OUTPUT inserted.NumberID as PhotoNumberID, inserted.ProductNumberID, inserted.SHA1, inserted.Rank INTO InsertedPhotos;