CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
adontz
adontz

Reputation: 1438

TSQL - MERGE statement with composite key

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?

Upvotes: 12

Views: 9638

Answers (2)

Andriy M
Andriy M

Reputation: 77657

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.

Upvotes: 15

Marcel N.
Marcel N.

Reputation: 13976

For starters, only columns from the target table can be used in the WHEN NOT MATCHED BY SOURCE additional merge condition (it's on MSDN).

And I think it's normal that you lose all extra entries from the target table, because they don't match anything in the source.

You should rewrite your query by first deleting the WHEN NOT MATCHED BY SOURCE clause and then deleting separately extra/unneeded rows.

Then, you need to get all entries that are updated or inserted in the target table by adding: 

DECLARE @OutputTable table( OrderId INT, OrderLine INT);

...Your entire MERGE
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        ([OrderID], [LineIndex], [Data])
    VALUES
        ([Source].[OrderID], [Source].[LineIndex], [Source].[Data])
OUTPUT INSERTED.OrderId, INSERTED.LineIndex INTO @OutputTable

Now in @OutputTable you have all keys that were either updated or entered in the target table (notice the OUTPUT clause).

You just need now to see which rows from the target table, that only match keys from the @OrderLines, are not in the @OutputTable' and delete them (so they haven't been updated nor inserted by theMERGE` statement):

DELETE A
FROM [OrderLines] AS A
INNER JOIN @OrderLines AS B
 ON B.OrderId = A.OrderId AND B.LineIndex = A.LineIndex
LEFT OUTER JOIN @OutputTable AS C
 ON C.OrderId = A.OrderId AND C.OrderLine = A.LineIndex
WHERE C.OrderId IS NULL AND C.OrderLine IS NULL

What you're doing here (think it's right) is actually what you wanted to delete in the first place. The inner join filters the result set to @OrderLines (so only rows with those keys) and the left join together with the where clause is doing an anti semi join, to get rows in target table that where not affected by the MERGE statement (insert or update) but still have keys that are in the source table (@OrderLines).

Should be right... Let me know after you test it.

You may want to wrap all this (MERGE + DELETE) inside a transaction, if you decide to go with this approach.

Upvotes: 1

Related Questions