Sql Server TVP Merge with Where/Case Statement

Question

What I am doing is pretty straight forward dug around at a few posts and couldn't figure out how to express it properly

TVP Declaration

 CREATE TYPE [dbo].[CustomSeoDic] as table (
 [RecordID] [int] NULL,
 [Name] [nvarchar](125) NULL)
 GO

Attempt 1

    BEGIN
        MERGE INTO Listings L
        USING @CustomSeo AS Tvp 
            ON Listings.ListingID = @CustomSeo.RecordID

        WHEN MATCHED AND L.OriginalSubdivisionName IS NULL THEN 
            UPDATE SET 
            L.OriginalSubdivisionName = L.SubdivisionName 
        WHEN MATCHED AND L.OriginalSubdivisionName IS NOT NULL THEN 
            UPDATE SET 
            L.SubdivisionName = Tvp.Name    
     END

Attempt 2

   BEGIN
        MERGE INTO Listings L
        USING @CustomSeo AS Tvp 
            ON Listings.ListingID = @CustomSeo.RecordID

        WHEN MATCHED AND L.OriginalSubdivisionName IS NULL THEN 
            UPDATE SET 
            CASE L.OriginalSubdivisionName IS NULL THEN L.OriginalSubdivisionName = L.SubdivisionName 
            CASE L.OriginalSubdivisionName IS NOT NULL THEN L.SubdivisionName = Tvp.Name    
    END

Error Received

Msg 10714, Level 15, State 1, Procedure Update_SubdivisionNames, Line 17 An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

Answer 1

You don't need a MERGE. You need conditional SET ColumnX = (this or that) statements.

Something like this.

Update Listings

Set L.OriginalSubdivisionName =
case when L.OriginalSubdivisionName IS NULL Then
L.SubdivisionName
else
L.OriginalSubdivisionName / * a little trick to keep it the same value */
end
,
L.SubdivisionName = 
case
    when L.OriginalSubdivisionName IS NOT NULL
        then Tvp.Name 
    else
    L.SubdivisionName / * a little trick to keep it the same value */
End

From
Listings L 
join
@CustomSeo AS Tvp 
            ON Listings.ListingID = @CustomSeo.RecordID

Answer 2

I am mostly guessing here but it seems like a simple update along these lines should be close to what you are trying to do.

UPDATE l
SET OriginalSubdivisionName = CASE WHEN l.OriginalSubdivisionName IS NULL THEN L.SubdivisionNam ELSE Tvp.Name END
FROM Listing l
JOIN @CustomSeo Tvp ON Tvp.RecordID = l.ListingID

Answer 3

Alternatively if you insist on MERGE statement:

BEGIN
    MERGE INTO Listings L
    USING @CustomSeo AS Tvp 
        ON Listings.ListingID = @CustomSeo.RecordID

    WHEN MATCHED 
        UPDATE SET 
        L.OriginalSubdivisionName = CASE WHEN L.OriginalSubdivisionName IS NULL 
                                         THEN L.SubdivisionName 
                                         ELSE Tvp.Name END
END