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;