MERGE Запрос и удаление записей

У меня есть таблица, которая выглядит примерно так:

AccountID, ItemID
1, 100
1, 200
2, 300

У меня есть proc, который принимает параметр значения таблицы, который обновляет элементы, связанные с учетной записью. Мы передадим что-то вроде следующего:

AccountID, ItemID
3, 100
3, 200

Прок выглядит примерно так:

procedure dbo.MyProc( @Items as dbo.ItemListTVP READONLY )
AS
BEGIN
  MERGE INTO myTable as target
    USING @Items
       on (Items.AccountId = target.AccountId)
       AND (Items.ItemId = target.ItemId)
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (AccountId, ItemId)
        VALUES (Items.AccountId, Items.ItemId)

   ;

END

На основе переданных данных я ожидаю, что он добавит две новые записи в таблицу, что и делает.

Я хочу иметь предложение WHEN NOT MATCHED BY SOURCE, которое удалит элементы для указанной учетной записи, которые не совпадают.

Например, если я передаю

AccountID, ItemID
1, 100
1, 400

Затем я хочу, чтобы он удалил запись, имеющую 1, 200; но оставить ВСЕ других.

Если я просто сделаю:

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

то он удалит все записи для учетных записей, не связанных (например: идентификаторы учетной записи 2 и 3).

Как я могу это сделать?

Спасибо,

Ответ 1

Я могу представить два очевидных способа, но оба они включают в себя повторную обработку TVP.

Во-первых, просто изменить условие DELETE

    WHEN NOT MATCHED BY SOURCE 
    AND target.AccountId IN(SELECT AccountId FROM @Items) THEN
        DELETE;

Во-вторых, использование CTE для ограничения целевого

WITH cte as
(
SELECT ItemId, AccountId 
FROM @myTable m
WHERE EXISTS 
  (SELECT * FROM @Items i WHERE i.AccountId = m.AccountId)
)
      MERGE INTO cte as target
        USING @Items Items
           ON (Items.AccountId = target.AccountId) AND
              (Items.ItemId = target.ItemId)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (AccountId, ItemId)
            VALUES (Items.AccountId, Items.ItemId)
         WHEN NOT MATCHED BY SOURCE THEN 
            DELETE;

Ответ 2

Надеюсь, что это поможет.

--  myTable
--  (
--      GroundID bigint, -- FK
--      GroupID, bigint, -- FK
--      AcceptingReservations bit
--  );

merge into myTable as target
using @tmpTable as source
    on  ( source.GroundID   = target.GroundID )
    and ( source.GroupID    = target.GroupID )
when
    not matched by target
    then
        insert ( GroundID, GroupID, AcceptingReservations )
        values
        (
            source.GroundID,
            source.GroupID,
            source.AcceptingReservations
        )
-- If there is a row that matches, update values;
when matched
    then
        update set
            target.AcceptingReservations = source.AcceptingReservations
-- If they do not match, delete for that GroundID only;
when
    not matched by source
    and target.GroundID = @GroundID
        then
            delete;

Ответ 3

Создайте переменную типа таблицы в базе данных sql

CREATE TYPE [dbo].[YourTableType] AS TABLE(
     [AccountID] [int] NULL,
     [ItemID] [int] NULL
     )
   GO

Внесите изменения в процедуру обновления

ALTER PROCEDURE YourProcedure
@Items YourTableType READONLY
AS
BEGIN
   MERGE INTO [dbo].[YourTable] as Target
   USING @Items as Source
ON 
    Target.[AccountID]=Source.[AccountID] and 
    Target.[ItemID]=Source.[ItemID] 
   WHEN NOT MATCHED by TARGET THEN
     INSERT 
        ([AccountID],
         [ItemID])
     VALUES 
       (Source.[AccountID],
        Source.[ItemID])

   WHEN NOT MATCHED BY SOURCE AND 
        target.[ItemID] IN(SELECT [ItemID] FROM @Items) 
THEN
    DELETE;

КОНЕЦ

Ответ 4

Вышеупомянутый ответ работает в описанной ситуации.

У меня есть таблица исключений, которую я использую для хранения исключений для счетов-фактур. Я хочу, чтобы он содержал текущие исключения для счета-фактуры. Поэтому, если я исправлю некоторые вещи в данных счета и снова запустил процесс, он создаст новый список исключений. Я хочу, чтобы он добавлял новые исключения, обновлял существующие и удалял исключения, которые больше не существуют. ТАКОЕ ДЛИННО, КАК ОНИ ПРИБЫВАЮТСЯ С ТОЧНЫМ СЧЕТОМ (или что-то еще).

Проблема заключалась в том, что оператор MERGE, КОГДА НЕ СОГЛАСЕН ИСТОЧНИКОМ ТОГО, УДАЛИТЬ, удалит все в таблице TARGET; а не только лишние предметы больше не используются в SOURCE! Я не мог квалифицировать инструкцию WHEN NOT MATCHED BY SOURCE, так что DELETE влияет только на номер счета-фактуры в TARGET, который больше не находится в SOURCE.

Ошибка сказала мне: "Разрешены только целевые столбцы в предложении" КОГДА НЕ СООТВЕТСТВУЕТ ИСТОЧНИК "инструкции MERGE."

Итак, вы должны определить строки TARGET с переменной.