在 SQL Server 中使用合并时，用 Update 子句替换多个"When Matched"

Question

我有这个Merge语句

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)

它给了我

类型为'WHEN MATCHED'的动作不能在MERGE语句的'UPDATE'子句

还有别的方法吗?

Answer 1

这在功能上与您所写的相同。

即:如果@delete=1，只更新被删除的列，否则更新所有属性

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

但是，如果我对@Source中的内容做出一些大胆的假设，我怀疑这很好:

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
)

Answer 2

如果@Deleted或其他长条件，则更新所有5列:

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