如何在 SQL Server 中使用 INNER JOIN 从多个表中删除 [英] How do I delete from multiple tables using INNER JOIN in SQL server

问题描述

在 MySQL 中你可以使用语法

In MySQL you can use the syntax

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

如何在 SQL Server 中做同样的事情?

How do I do the same thing in SQL Server?

推荐答案

您可以利用此示例中已删除"的伪表.类似的东西:

You can take advantage of the "deleted" pseudo table in this example. Something like:

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;

显然你可以做一个'输出删除'.如果您需要为第三个表加入某些内容，也可以在第二次删除时进行.

Obviously you can do an 'output deleted.' on the second delete as well, if you needed something to join on for the third table.

作为旁注，您还可以在 insert 语句上执行 insert.* ，并在 update 语句上执行 insert.* 和 deleted.* .

As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.

另外，您是否考虑过在 table1 上添加触发器以从 table2 + 3 中删除?您将处于隐式事务中，并且还可以使用inserted."和deleted."伪表.

Also, have you considered adding a trigger on table1 to delete from table2 + 3? You'll be inside of an implicit transaction, and will also have the "inserted." and "deleted." pseudo-tables available.

这篇关于如何在 SQL Server 中使用 INNER JOIN 从多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！