Fusionner des requêtes et supprimer des enregistrements

j'ai une table qui ressemble à quelque chose comme:

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

j'ai un proc qui accepte un paramètre table value qui met à jour les éléments associés à un compte. Nous allons passer à quelque chose comme:

AccountID, ItemID
3, 100
3, 200

Le proc ressemble à quelque chose comme:

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

basé sur les données transmises, Je m'attends à ce qu'il ajoute 2 nouveaux enregistrements à la table, ce qu'il fait.

ce que je veux, c'est avoir une clause WHEN NOT MATCHED BY SOURCE qui supprimera les éléments pour le compte spécifié qui ne correspondent pas.

Par exemple, si je passe

AccountID, ItemID
1, 100
1, 400

puis je veux qu'il efface l'enregistrement ayant 1, 200; mais laisser tous les autres.

Si je viens de faire:

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

puis il supprimera tous les enregistrements pour les comptes non référencés (c'est-à-dire les ID de compte 2 et 3).

Comment puis-je faire cela?

Merci,

23
demandé sur Martin Smith 2011-03-04 20:31:45

4 réponses

je peux penser à deux façons évidentes, mais les deux impliquent le traitement de la TVP à nouveau.

le premier est simplement de changer le DELETE condition

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

La seconde est d'utiliser un CTE pour restreindre la cible

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;
39
répondu Martin Smith 2011-03-04 18:30:38

Espérons que cette aide.

--  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;
4
répondu dbaourdos 2013-12-29 20:41:16

Créer un tableau de type de variable dans la base de données sql

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

apporter des modifications à votre procédure de mise à jour

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;

2
répondu Athul Nalupurakkal 2017-02-13 06:02:46

la réponse ci-dessus fonctionne dans la situation décrite.

j'ai une table d'exception que j'utilise pour stocker les exceptions aux factures. Je veux seulement qu'il contienne les exceptions actuelles pour la facture. Ainsi, si je corrige certaines choses dans les données de la facture et exécute le processus à nouveau, il créera une nouvelle liste d'exceptions. Je veux qu'il ajoute les nouvelles exceptions, mette à jour celles existantes, et supprime les exceptions qui n'existent plus-tant qu'elles appartiennent à la même facture (ou quoi.)

le problème que j'ai eu était que la déclaration de fusion quand il N'y avait pas de correspondance par SOURCE puis supprimer supprimerait tout dans la table cible; pas seulement les éléments supplémentaires ne sont plus dans la SOURCE! Je n'ai pas pu qualifier L'énoncé "WHEN NOT MATCHED BY SOURCE" de sorte que la suppression n'affecterait que le même numéro de facture dans la cible qui n'était plus dans la SOURCE.

une erreur m'a été signalée "seules les colonnes cibles sont autorisées dans la clause' WHEN NOT MATCHED BY SOURCE ' d'une Instruction MERGE."

vous devez donc qualifier les lignes cibles avec une variable.

0
répondu Scott 2014-02-10 23:07:32