subconsulta - ¿Es posible eliminar de varias tablas en la misma instrucción SQL?

eliminar varios registros sql server

Question

Es posible eliminar el uso de instrucciones de unión para calificar el conjunto que se eliminará, como por ejemplo:

DELETE J
FROM Users U
inner join LinkingTable J on U.id = J.U_id
inner join Groups G on J.G_id = G.id

WHERE G.Name = ''Whatever''
and U.Name not in (''Exclude list'')

Sin embargo, estoy interesado en eliminar ambos lados de los criterios de unión, tanto el registro de la LinkingTable registro del usuario del que depende. No puedo activar las cascadas porque mi solución es primero el código de Entity Framework y las relaciones bidireccionales conforman varias rutas en cascada.

Idealmente, me gustaría algo como:

DELETE J, U
FROM Users U
inner join LinkingTable J on U.id = J.U_id
...

Sintácticamente esto no funciona, pero tengo curiosidad si algo como esto es posible?

Answer 1

La única forma en que podría pensar es romper lógicamente las claves externas bidireccionales de manera procesal .

Este enfoque puede tener un gran impacto en el lado de la aplicación si no tiene algunas marcas para el estado o estado de visualization

Algo como

1. INSERT filas simuladas no visibles para los usuarios (con algo como Id = -1 para valores ficticios)

2. Agregar a LinkingTable una columna alternativa para señalar a los Users , lo llamaré U_ComesFrom

   ALTER TABLE LinkingTagble ADD U_ComesFrom_U_id INT DEFAULT (-1)

3. Añadir la FOREIGN KEY NOCHECK con un NOCHECK

   ALTER TABLE LinkingTable CON NOCHECK
   LLAVE EXTRANJERA (U_ComesFrom_U_id)
   REFERENCIAS Usuarios (Id);

4. Añadir a la columna Users

   ALTERAR MESA Usuarios AGREGAR MarkedForDeletion BIT NOT NULL DEFAULT (0)

Entonces tu SQL se vería como

BEGIN TRANSACTION
    UPDATE J
    SET U_Comes_From_U_id = U_ID, U_id = -1 -- or some N/R value that you define in Users
    FROM Users U
    inner join LinkingTable J on U.id = J.U_id
    inner join Groups G on J.G_id = G.id
    WHERE G.Name = ''Whatever''
    and U.Name not in (''Exclude list'')

    UPDATE U
    SET MarkedForDeletion = 1
    FROM Users
    inner join LinkingTable J on U.id = J.U_ComesFrom_U_id 
    WHERE U_id > 0

    DELETE FROM LinkingTable 
    WHERE U_ComesFrom_U_id > 0

    DELETE FROM Users
    WHERE MarkedForDeletion = 1

COMMIT

Este enfoque afectaría el rendimiento, ya que cada transacción sería al menos 4 operaciones DML por claves bidireccionales.

Answer 2

La forma en que dices es posible en MY SQL pero no para SQL SERVER

Puede utilizar la pseudo tabla "eliminada" para eliminar los valores de Dos tablas a la vez, como

 begin transaction;

 declare @deletedIds table ( samcol1 varchar(25) );

 delete #temp1
 output deleted.samcol1 into @deletedIds
 from #temp1 t1
 join #temp2 t2
 on t2.samcol1 = t1.samcol1

 delete #temp2
 from #temp2 t2
 join @deletedIds d
 on d.samcol1 = t2.samcol1;

 commit transaction;

Para una breve explicación, puede echar un vistazo a este Link

y para saber el uso de la tabla eliminada, puede seguir esto utilizando las tablas insertadas y eliminadas

Answer 3

No, necesitarías ejecutar varias declaraciones.

Debido a que necesita eliminar de dos tablas, considere crear una tabla temporal de los identificadores coincidentes:

SELECT U.Id INTO #RecordsToDelete
FROM Users U
   JOIN LinkingTable J ON U.Id = J.U_Id
...

Y luego eliminar de cada una de las tablas:

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

Answer 4

Si está creando la clave externa a través de T-SQL, debe agregar la opción ON DELETE CASCADE a la clave externa:

Code Snippet 

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;

Answer 5

Utilice TRY CATCH con Transacción

BEGIN TRANSACTION
BEGIN TRY
    DELETE from A WHERE id=1

    DELETE FROM b WHERE id=1

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

o también puede usar el procedimiento Almacenar para el mismo Uso de procedimientos almacenados con transacción: