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.

You might reconsider using MERGE for this. From attempt #1 it looks like all you need to do is write an update with an ISNULL — Sean Lange, Jun 11 '15 at 14:23
IMHO, there is no reason to write a MERGE statement when there is no corresponding "not matched". When all you have is "matched", its just an update statement. Why complicate it ? — granadaCoder, Jun 11 '15 at 15:25
Ok. Yeah, I'd "retreat" a tad from the examples... and just do an Update statement. Glad you got it working. — granadaCoder, Jun 11 '15 at 16:05
Here is another MERGE tidbit..I learned the hard way. (Thus another reason to only use it when you really need it) ::: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx — granadaCoder, Jun 11 '15 at 16:06

score 2 · Answer 1 · answered Jun 11 '15 at 14:28

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

answered Jun 11 '15 at 14:28

Sean Lange

33,028
3
25
40

Perfect Thank you :), Most examples of tvp use Merge, I was just trying to work with the kvp returned thank you. – Keith Beard Jun 11 '15 at 14:33
MERGE actually has some major problems. A bit off topic but still relevant. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Sean Lange Jun 11 '15 at 14:34

score 2 · Answer 2 · answered Jun 11 '15 at 14:35

2

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

answered Jun 11 '15 at 14:35

Giorgi Nakeuri

35,155
8
47
75

Upvote because you did it with a merge, I just couldn't get the Case right, Thank you as well – Keith Beard Jun 11 '15 at 14:40

granadaCoder · Accepted Answer · 2015-06-11T16:20:02.067

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

Sql Server TVP Merge with Where/Case Statement

3 Answers3