当匹配时类型的动作＆＃x27;在A＆＃x27; Update＆＃x27;无法出现不止一次。合并语句的条款

Question

当我尝试进行操作类型时，我会遇到此错误。我已经阅读了一些答案和文章，介绍了为什么会发生这种情况，并且在与该语句的删除或更新时，MSDN不允许。

但是，我找不到问题的答案。有人可以指导我在这里可以做什么。

匹配和更新条件时，我有一个与两个合并语句，这就是我所需要的。关于我如何更好地编码的任何想法？

MERGE [Digibill].[MertleUsedLinkys] AS [Target]
USING [Staging].[MertleUsedLinkys] AS [Source]
ON [Source].[LinkyCode] = [Target].[LinkyCode]
WHEN MATCHED  AND CONVERT(DATETIME, [Source].[LinkyPiTRunDateUTC]) > [Target].[LinkyPiTRunDateUTC]
THEN
 UPDATE SET [Target].[LinkyPiTRunDateUTC] = [LinkyPiTRunDateUTC],
            [Target].[LinkyQty] = [Source].[LinkyQty]


WHEN MATCHED AND CONVERT(DATETIME, [Source].[LinkyPiTRunDateUTC]) <= [Target].[LinkyPiTRunDateUTC]
THEN 
UPDATE SET 
            [Target].[LinkyCode] = [Source].[LinkyCode],
            [Target].[ServiceKey] = [Source].[ServiceKey],
            [Target].[CustName] = [Source].[CustName],
            [Target].[SupplyRegion] = [Source].[SupplyRegion],
            [Target].[ServiceStatus] = [Source].[ServiceStatus],
            [Target].[NS_ExtID] = [Source].[NS_ExtID],
            [Target].[PartitionKey] = [Source].[PartitionKey],
            [Target].[BillingMthly_PIT] = [Source].[BillingMthly_PIT],
            [Target].[CurrentBillingPeriod] = [Source].[CurrentBillingPeriod],
            [Target].[LinkyPiTRunDateUTC] = [Source].[LinkyPiTRunDateUTC],
            [Target].[CurrBillingPeriodStatus] = [Source].[CurrBillingPeriodStatus],
            [Target].[LinkyQty] = [Source].[LinkyQty]


         
WHEN NOT MATCHED THEN
        INSERT (    
        [LinkyCode] ,
    [ServiceKey] ,
    [CustName],
    [SupplyRegion],
    [ServiceStatus],
    [NS_ExtID],
    [PartitionKey],
    [BillingMthly_PIT],
    [CurrentBillingPeriod],
    [LinkyPiTRunDateUTC],
    [CurrBillingPeriodStatus],
    [LinkyQty]
)

 VALUES 
 ([Source].[LinkyCode], [Source].[ServiceKey], [Source].[CustName], [Source].[SupplyRegion], [Source].[ServiceStatus], [Source].[NS_ExtID], [Source].[PartitionKey],
    [Source].[BillingMthly_PIT], [Source].[CurrentBillingPeriod],CONVERT(DATETIME,[Source].[LinkyPiTRunDateUTC]),[Source].[CurrBillingPeriodStatus],[Source].[LinkyQty]
)

END;

显然，当我运行代码时，我会收到错误。任何想法，潜在客户或解决方法都将受到赞赏。

编辑：

    AS
BEGIN 


MERGE [Digibill].[MertleUsedLinkys] AS [Target]
USING [Staging].[MertleUsedLinkys] AS [Source]
ON [Source].[LinkyCode] = [Target].[LinkyCode]
WHEN MATCHED
THEN 
UPDATE SET
            [Target].[LinkyPiTRunDateUTC] = CASE WHEN [Source].[LinkyPiTRunDateUTC] > [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyPiTRunDateUTC] 
            ELSE [Target].[LinkyPiTRunDateUTC]
            END,
            [Target].[LinkyQty] = CASE WHEN [Source].[LinkyPiTRunDateUTC] > [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyQty] 
            ELSE [Target].[LinkyQty]
            END,
            [Target].[LinkyCode] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyCode]
            ELSE [Target].[LinkyCode] 
            END,
            [Target].[ServiceKey] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[ServiceKey]
            ELSE [Target].[ServiceKey]
            END,
            [Target].[CustName] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[CustName]
            ELSE [Target].[CustName]
            END,
            [Target].[SupplyRegion] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[SupplyRegion]
            ELSE [Target].[SupplyRegion]
            END,
            [Target].[ServiceStatus] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[ServiceStatus]
            ELSE  [Target].[ServiceStatus]
            END,
            [Target].[NS_ExtID] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[NS_ExtID]
            ELSE [Target].[NS_ExtID]
            END,
            [Target].[PartitionKey] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[PartitionKey]
            ELSE [Target].[PartitionKey]
            END,
            [Target].[BillingMthly_PIT] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[BillingMthly_PIT]
            ELSE [Target].[BillingMthly_PIT]
            END,
            [Target].[CurrentBillingPeriod] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[CurrentBillingPeriod]
            ELSE [Target].[CurrentBillingPeriod]
            END,
            --[Target].[LinkyPiTRunDateUTC] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyPiTRunDateUTC]
            --ELSE [Target].[LinkyPiTRunDateUTC]
            --END,
            [Target].[CurrBillingPeriodStatus] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[CurrBillingPeriodStatus]
            ELSE [Target].[CurrBillingPeriodStatus]
            END
            --[Target].[LinkyQty] = CASE WHEN [Source].[LinkyPiTRunDateUTC] <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyQty]
            --ELSE [Target].[LinkyQty]
            --END


        WHEN NOT MATCHED THEN
        INSERT (    
        [LinkyCode] ,
    [ServiceKey] ,
    [CustName],
    [SupplyRegion],
    [ServiceStatus],
    [NS_ExtID],
    [PartitionKey],
    [BillingMthly_PIT],
    [CurrentBillingPeriod],
    [LinkyPiTRunDateUTC],
    [CurrBillingPeriodStatus],
    [LinkyQty]
)

 VALUES 
 ([Source].[LinkyCode], [Source].[ServiceKey], [Source].[CustName], [Source].[SupplyRegion], [Source].[ServiceStatus], [Source].[NS_ExtID], [Source].[PartitionKey],
    [Source].[BillingMthly_PIT], [Source].[CurrentBillingPeriod],[Source].[LinkyPiTRunDateUTC],[Source].[CurrBillingPeriodStatus],[Source].[LinkyQty]
);

END;

Answer 1

根据您当前作为“匹配”分支的一部分，在更新中使用案例表达式。即，对于仅在一个分支中更新的内容，请使用案例表达式将其更新，或者将其保持相同。这是您的一列之一的示例。

[Target].[LinkyCode] =
    CASE
    WHEN CONVERT(DATETIME, [Source].[LinkyPiTRunDateUTC]) <= [Target].[LinkyPiTRunDateUTC] THEN [Source].[LinkyCode]
    ELSE [TARGET].[LinkyCode]
    END

Answer 2

鉴于逻辑是＆gt;或＆lt; =，那么

....
WHEN MATCHED
THEN 
UPDATE SET ....

如果其中一个列可能为null， 则可以删除其中一个条件相同。您需要将其从源中删除

...
USING (
    SELECT Source.*
    FROM Staging.MertleUsedLinkys AS Source
    WHERE Source.LinkyPiTRunDateUTC] IS NOT NULL
) AS Source
...