TSQL - MERGE statement with composite key

sql-server, sql-server-2012, t-sql

Solution

Just use the relevant subset of `OrderLines` as the target:

WITH AffectedOrderLines AS (
    SELECT *
    FROM OrderLines
    WHERE OrderID IN (SELECT OrderID FROM @OrderLines)
)
MERGE
    AffectedOrderLines AS [Target]
USING
(
    SELECT
        [OrderID], [LineIndex], [Data]
    FROM
        @OrderLines
)
AS [Source] ([OrderID], [LineIndex], [Data])
ON ([Target].[OrderID] = [Source].[OrderID]) AND ([Target].[LineIndex] = [Source].[LineIndex])
WHEN MATCHED THEN
    UPDATE
    SET
        [Target].[Data] = [Source].[Data]
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        ([OrderID], [LineIndex], [Data])
    VALUES
        ([Source].[OrderID], [Source].[LineIndex], [Source].[Data])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

And here's a SQL Fiddle to test.

Problem

I have table OrderLines(OrderID int, LineIndex int, ) and table valued parameter of the same structure defining new order lines for one order. So if I had the following OrderLines ``` 1000 1 bread 1000 2 milk 1001 1 oil 1001 2 yogurt 1002 1 beef 1002 2 pork ``` and the following TVP ``` 1001 1 yogurt ``` I want to get the following OrderLines ``` 1000 1 bread 1000 2 milk 1001 1 yogurt 1002 1 beef 1002 2 pork ``` I.e. touch rows only for one Order. So I wrote my query like this ``` MERGE [OrderLines] AS [Target] USING ( SELECT [OrderID], [LineIndex], [Data] FROM @OrderLines ) AS [Source] ([OrderID], [LineIndex], [Data]) ON ([Target].[OrderID] = [Source].[OrderID]) AND ([Target].[LineIndex] = [Source].[LineIndex]) WHEN MATCHED THEN UPDATE SET [Target].[Data] = [Source].[Data] WHEN NOT MATCHED BY TARGET THEN INSERT ([OrderID], [LineIndex], [Data]) VALUES ([Source].[OrderID], [Source].[LineIndex], [Source].[Data]) WHEN NOT MATCHED BY SOURCE THEN DELETE; ``` and it deletes all other (not mentioned) OrderLines for other Orders. I tried ``` WHEN NOT MATCHED BY SOURCE AND ([Target].[OrderID] = [Source].[OrderID]) THEN ``` but got a syntactic error. How should I rewrite my query?

Original source