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

user_18017566 0
Viewed 59

I get this error when I try to do action type of WHEN MATCHED more than once. I have read a few answers and articles on why this happens and MSDN doesn't allow WHEN MATCHED unless its a delete or update with the statement.

However, I fail to find answers for my problem. Could someone please guide me on what I can do here.

I have a merge statement with two WHEN MATCHED and Update conditions and that is what I need. Any ideas of how I can better code this?

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;

Obviously when I run the code I get the error. Any ideas, leads or workarounds are appreciated.

EDIT:

    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;
sql-server sql
2 Answers

Use CASE expressions in your update based on the condition you currently have as part of your 'MATCHED' branch. i.e. for the ones you only update in one branch use the case expression to either update it, or leave it the same. Here is an example of one of your columns.

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

Given that the logic is either > or <= then you can just remove one of the conditions

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

If one of those columns could possibly be null then the logic is not quite the same. You would need to remove it from the source to start with

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