sql-server - TSQL - 带有复合键的 MERGE 语句

Question

我有表 OrderLines(OrderID int, LineIndex int, ) 和相同结构的表值参数定义一个订单的新订单行。

所以如果我有以下 OrderLines

1000   1   bread
1000   2   milk
1001   1   oil
1001   2   yogurt
1002   1   beef
1002   2   pork

以及以下 TVP

1001   1   yogurt

我想获得以下 OrderLines

1000   1   bread
1000   2   milk
1001   1   yogurt
1002   1   beef
1002   2   pork

IE。仅针对一个订单触摸行。

所以我写了这样的查询

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;

并删除其他订单的所有其他(未提及)订单行。

我试过

WHEN NOT MATCHED BY SOURCE AND ([Target].[OrderID] = [Source].[OrderID]) THEN

但有一个语法错误。

我应该如何重写我的查询？

Answer 1

只需使用 OrderLines 的相关子集作为目标:

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;

和 here's a SQL Fiddle去测试。