Substitute for multiple "When Matched" with Update clause when using Merge in SQL Server-CodePudding

I have this Merge statement

MERGE Destination d
USING @Source s
    ON d.DestinationId = s.DestinationId

WHEN MATCHED AND (
    ISNULL(d.DestinationFieldOne,0) != ISNULL(s.DestinationFieldOne,0) OR
    ISNULL(d.DestinationFieldTwo,'') != ISNULL(s.DestinationFieldTwo,'') OR
    ISNULL(d.DestinationFieldThree,'') != ISNULL(s.DestinationFieldThree,'') OR
    ISNULL(d.DestinationFieldFour,'') != ISNULL(s.DestinationFieldFour,'')

THEN UPDATE SET
    d.DestinationFieldOne = s.DestinationFieldOne,
    d.DestinationFieldTwo = s.DestinationFieldTwo,
    d.DestinationFieldThree = s.DestinationFieldThree,
    d.DestinationFieldFour = s.DestinationFieldFour
WHEN MATCHED AND (
    @Deleted = 1
)
THEN UPDATE SET
    d.Deleted = 1
WHEN NOT MATCHED BY TARGET
    THEN INSERT (DestinationFieldOne, DestinationFieldTwo, DestinationFieldThree, DestinationFieldFour) VALUES (s.DestinationFieldOne, s.DestinationFieldTwo, s.DestinationFieldThree, s.DestinationFieldFour)

It's giving me

An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

Is there any other way to do it?

CodePudding user response：

This is functionally the same as what you've written.

Which is: if @delete=1, only update the deleted column otherwise update all attributes

UPDATE Destination
SET 
    -- leave as old values if Deleted=1
    d.DestinationFieldOne =
        IIF(s.Deleted=1,d.DestinationFieldOne,s.DestinationFieldOne),
    d.DestinationFieldTwo = 
        IIF(s.Deleted=1,d.DestinationFieldTwo,s.DestinationFieldTwo),
    d.DestinationFieldThree = 
        IIF(s.Deleted=1,d.DestinationFieldThree,s.DestinationFieldThree),
    d.Deleted = IIF(s.Deleted=1,1,d.Deleted)
FROM Destination d
INNER JOIN @Source s
ON d.DestinationId = s.DestinationId

But, if i make some bold assumptions about what's in @Source, I suspect this is fine:

UPDATE Destination
SET 
    d.DestinationFieldOne = s.DestinationFieldOne,
    d.DestinationFieldTwo = s.DestinationFieldTwo,
    d.DestinationFieldThree = s.DestinationFieldThree,
    d.Deleted = s.Deleted
FROM Destination d
INNER JOIN @Source s
ON d.DestinationId = s.DestinationId


INSERT INTO Destination (
DestinationFieldOne, DestinationFieldTwo, 
DestinationFieldThree, DestinationFieldFour) 
SELECT s.DestinationFieldOne, s.DestinationFieldTwo, 
s.DestinationFieldThree, s.DestinationFieldFour
FROM @Source S WHERE NOT EXISTS (
    SELECT * FROM Destination D WHERE S.DestinationId = D.DestinationId
    )

CodePudding user response：

If either @Deleted or the other long condition, update all 5 columns:

UPDATE d set
     DestinationFieldOne    = case DestinationUpdateFlag when 1 then s.DestinationFieldOne      else d.DestinationFieldOne      end         
    ,DestinationFieldTwo    = case DestinationUpdateFlag when 1 then s.DestinationFieldTwo      else d.DestinationFieldTwo      end     
    ,DestinationFieldThree  = case DestinationUpdateFlag when 1 then s.DestinationFieldThree    else d.DestinationFieldThree    end
    ,DestinationFieldFour   = case DestinationUpdateFlag when 1 then s.DestinationFieldFour     else d.DestinationFieldFour     end
    ,Deleted                = case @Deleted when 1 then 1 else d.Deleted end
from 
    Destination d
    INNER JOIN @Source s ON d.DestinationId = s.DestinationId
    cross apply
    (select case when 
        ISNULL(d.DestinationFieldOne,0) != ISNULL(s.DestinationFieldOne,0) OR
        ISNULL(d.DestinationFieldTwo,'') != ISNULL(s.DestinationFieldTwo,'') OR
        ISNULL(d.DestinationFieldThree,'') != ISNULL(s.DestinationFieldThree,'') OR
        ISNULL(d.DestinationFieldFour,'') != ISNULL(s.DestinationFieldFour,'')
    then 1 else 0 end as DestinationUpdateFlag
    ) as q1
where @Deleted = 1 or DestinationUpdateFlag=1