MERGE - Multiple WHEN MATCHED cases with update

Question

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.

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;

Answer 1

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)

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;

Answer 2

If you can, use CASE expressions in your UPDATE sub-statements to mimic the behavior of having multiple WHEN MATCHED clauses. Something like this:

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 CASE expressions. 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.

Answer 3

Have you considered using CASE statement when doing an update?

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;

Answer 4

Why don't you try using CASE statement,

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;