在SQLServer中使用Merge時用Update子句替換多個“匹配時”

我有這個合并宣告

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”子句中出現多次。

還有其他方法嗎？

uj5u.com熱心網友回復：

這在功能上與您撰寫的內容相同。

即：如果@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
    )

uj5u.com熱心網友回復：

如果 @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

標籤：sql sql服务器 tsql

上一篇：SQLServer動態創建全域臨時表并插入資料問題

下一篇：透視一列并顯示另一列的關聯值