sql server - w3schools - MERGE Consulta y borrando registros.

sql server merge when matched and

Question

Tengo una mesa que se parece a algo como:

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

Tengo un proceso que acepta un parámetro de valor de tabla que actualiza los elementos asociados con una cuenta. Pasaremos algo como lo siguiente:

AccountID, ItemID
3, 100
3, 200

El proc se ve algo así como:

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

Sobre la base de los datos pasados, espero que agregue 2 registros nuevos a la tabla, lo que hace.

Lo que quiero es tener una cláusula WHEN NOT MATCHED BY SOURCE que eliminará los elementos de la cuenta especificada que no coincidan.

Por ejemplo, si paso

AccountID, ItemID
1, 100
1, 400

Entonces quiero que borre el registro que tiene 1, 200; pero deja TODOS los demás.

Si acabo de hacer:

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

luego eliminará todos los registros de las cuentas a las que no se hace referencia (es decir, los ID de cuenta 2 y 3).

¿Cómo puedo hacer esto?

Gracias,

Answer 1

Crear una variable de tipo de tabla en la base de datos sql

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

Haga cambios en su procedimiento de actualización

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;

FIN

Answer 2

Espero que esto ayude.

--  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;

Answer 3

La respuesta anterior funciona en la situación que se describe.

Tengo una tabla de excepciones que utilizo para almacenar excepciones a las facturas. Solo quiero que contenga las excepciones actuales para la factura. Entonces, si soluciono algunas cosas en los datos de la factura y ejecuto el proceso nuevamente, se creará una nueva lista de excepciones. Quiero que agregue las nuevas excepciones, actualice las existentes y elimine las excepciones que ya no existen, TANTO QUE ELLOS PERTENGAN LA MISMA FACTURA (o lo que sea).

El problema que tuve fue que la instrucción MERGE CUANDO NO SE PARTICIPAR POR LA FUENTE ENTONCES BORRAR eliminaría todo en la tabla TARGET; ¡No solo los artículos extra ya no están en la FUENTE! No pude calificar la declaración de CUANDO NO HAYA PARTIDO POR LA FUENTE, de modo que el BORRAR solo afectaría el mismo número de factura en el OBJETIVO que ya no estaba en la FUENTE.

Un error me dijo "Solo se permiten las columnas de destino en la cláusula ''WHEN NOT MATCHED BY SOURCE'' de una declaración MERGE".

Así que tienes que calificar las filas OBJETIVO con una variable.

Answer 4

Puedo pensar en dos formas obvias, pero ambas involucran el procesamiento del TVP nuevamente.

Lo primero es simplemente cambiar la condición DELETE .

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

El segundo es usar un CTE para restringir el objetivo

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;