如何在SQL Server中使用INNER JOIN从多个表中删除

Question

在MySQL中，您可以使用语法

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

如何在SQL Server中执行相同的操作？

Answer 1

在此示例中，您可以利用“已删除”伪表。就像是：

begin transaction;

   declare @deletedIds table ( id int );

   delete from t1
   output deleted.id into @deletedIds
   from table1 as t1
    inner join table2 as t2
      on t2.id = t1.id
    inner join table3 as t3
      on t3.id = t2.id;

   delete from t2
   from table2 as t2
    inner join @deletedIds as d
      on d.id = t2.id;

   delete from t3
   from table3 as t3 ...

commit transaction;

显然，您可以执行“已删除输出”。如果您需要在第三张表上加入某些内容，也可以在第二个删除上使用。

作为附带说明，您也可以在插入语句上执行insert。，在更新语句上同时进行insert。和Deleted。*。

编辑： 另外，您是否考虑过在table1上添加触发器以从table2 + 3中删除？您将处于隐式事务内，并且还将具有“插入的”和“删除的”伪表。