TSQL — оператор MERGE с составным ключом

У меня есть таблица OrderLines(OrderID int, LineIndex int,) и табличный параметр той же структуры, определяющий новые строки заказа для одного заказа.

Итак, если бы у меня были следующие OrderLines

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

и следующее ТВП

1001   1   yogurt

Я хочу получить следующие OrderLines

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

т.е. сенсорные строки только для одного заказа.

Итак, я написал свой запрос следующим образом

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

но получил синтаксическую ошибку.

Как мне переписать мой запрос?


sql-server tsql sql-server-2012
person adontz    schedule 04.07.2012    source источник

Ответы (2)


arrow_upward
13
arrow_downward

Просто используйте соответствующее подмножество 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;

И вот скрипт SQL для тестирования.

person Andriy M    schedule 05.07.2012

arrow_upward
1
arrow_downward

Во-первых, в WHEN NOT MATCHED BY SOURCE дополнительном условии слияния можно использовать только столбцы из целевой таблицы (это на MSDN).

И я думаю, что это нормально, что вы теряете все лишние записи из целевой таблицы, потому что они ничему не соответствуют в исходной.

Вы должны переписать свой запрос, сначала удалив предложение WHEN NOT MATCHED BY SOURCE, а затем удалив отдельно лишние/ненужные строки.

Затем вам нужно получить все записи, которые были обновлены или вставлены в целевую таблицу, добавив:

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

Теперь в @OutputTable у вас есть все ключи, которые были либо обновлены, либо введены в целевую таблицу (обратите внимание на предложение OUTPUT).

Теперь вам просто нужно увидеть, какие строки из целевой таблицы, которые соответствуют только ключам из @OrderLines, не находятся в операторе @OutputTable' and delete them (so they haven't been updated nor inserted by theMERGE`):

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

То, что вы делаете здесь (думаю, что это правильно), на самом деле то, что вы хотели удалить в первую очередь. Внутреннее соединение фильтрует результирующий набор на @OrderLines (поэтому только строки с этими ключами), а левое соединение вместе с предложением where выполняет анти-полусоединение, чтобы получить строки в целевой таблице, которые не затронуты оператором MERGE (insert или update) но по-прежнему имеют ключи из исходной таблицы (@OrderLines).

Должно быть правильно... Дайте мне знать после проверки.

Вы можете захотеть обернуть все это (MERGE + DELETE) внутри транзакции, если вы решите использовать этот подход.

person Marcel N.    schedule 04.07.2012