如何在 SQL Server 中使用 INNER JOIN 从多个表中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/783726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I delete from multiple tables using INNER JOIN in SQL server
提问by Byron Whitlock
In MySQL you can use the syntax
在 MySQL 中，您可以使用语法
DELETE t1,t2
FROM table1 AS t1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...
How do I do the same thing in SQL Server?
我如何在 SQL Server 中做同样的事情？
回答by John Gibb
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.
显然你可以做一个'输出删除'。在第二次删除时，如果您需要为第三个表加入某些内容。
As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.
作为旁注，您还可以在 insert 语句上执行 insert.* ，并在 update 语句上执行 insert.* 和 deleted.* 。
EDIT: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.
编辑：另外，您是否考虑过在 table1 上添加触发器以从 table2 + 3 中删除？您将处于隐式事务的内部，并且还可以使用“插入”和“删除”伪表。
回答by Aaron Daniels
You can always set up cascading deletes on the relationships of the tables.
You can encapsulate the multiple deletes in one stored procedure.
You can use a transaction to ensure one unit of work.
您始终可以对表的关系设置级联删除。
您可以将多个删除操作封装在一个存储过程中。
您可以使用事务来确保一个工作单元。
回答by topchef
You can use JOIN syntax in FROM clause in DELETE in SQL Server but you still delete from first table only and it's proprietary Transact-SQL extension which is alternative to sub-query.
您可以在 SQL Server 的 DELETE 中的 FROM 子句中使用 JOIN 语法，但您仍然只能从第一个表中删除，并且它是专有的 Transact-SQL 扩展，可以替代子查询。
From example here:
从这里的例子：
-- Transact-SQL extension
DELETE
FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN
Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
回答by Pavel Hodek
Example for delete some records from master table and corresponding records from two detail tables:
从主表中删除一些记录并从两个明细表中删除相应记录的示例：
BEGIN TRAN
-- create temporary table for deleted IDs
CREATE TABLE #DeleteIds (
Id INT NOT NULL PRIMARY KEY
)
-- save IDs of master table records (you want to delete) to temporary table
INSERT INTO #DeleteIds(Id)
SELECT DISTINCT mt.MasterTableId
FROM MasterTable mt
INNER JOIN ...
WHERE ...
-- delete from first detail table using join syntax
DELETE d
FROM DetailTable_1 D
INNER JOIN #DeleteIds X
ON D.MasterTableId = X.Id
-- delete from second detail table using IN clause
DELETE FROM DetailTable_2
WHERE MasterTableId IN (
SELECT X.Id
FROM #DeleteIds X
)
-- and finally delete from master table
DELETE d
FROM MasterTable D
INNER JOIN #DeleteIds X
ON D.MasterTableId = X.Id
-- do not forget to drop the temp table
DROP TABLE #DeleteIds
COMMIT
回答by Michael Buen
Just wondering.. is that really possible in MySQL? it will delete t1 and t2? or I just misunderstood the question.
只是想知道.. 在 MySQL 中真的有可能吗？它会删除t1和t2吗？或者我只是误解了这个问题。
But if you just want to delete table1 with multiple join conditions, just don't alias the table you want to delete
但是如果你只想删除带有多个连接条件的table1，就不要给你要删除的表加上别名
this:
这个：
DELETE t1,t2
FROM table1 AS t1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...
should be written like this to work in MSSQL:
应该这样写才能在 MSSQL 中工作：
DELETE table1
FROM table1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...
to contrast how the other two common RDBMS do a delete operation:
对比其他两个常见的 RDBMS 如何进行删除操作：
http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html
http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html
回答by Yishai
Basically, no you have to make three delete statements in a transaction, children first and then parents. Setting up cascading deletes is a good idea if this is not a one-off thing and its existence won't conflict with any existing trigger setup.
基本上，不，您必须在事务中执行三个删除语句，首先是子项，然后是父项。如果这不是一次性的事情并且它的存在不会与任何现有的触发器设置冲突，那么设置级联删除是一个好主意。
回答by tiny
In SQL server there is no way to delete multiple tables using join. So you have to delete from child first before delete form parent.
在 SQL Server 中，无法使用 join 删除多个表。因此，您必须先从 child 中删除，然后再从 parent 中删除。
回答by hidden
This is an alternative way of deleting records without leaving orphans.
这是删除记录而不留下孤儿的另一种方法。
Declare @user Table(keyValue int , someString varchar(10))
insert into @user
values(1,'1 value')
insert into @user
values(2,'2 value')
insert into @user
values(3,'3 value')
Declare @password Table( keyValue int , details varchar(10))
insert into @password
values(1,'1 Password')
insert into @password
values(2,'2 Password')
insert into @password
values(3,'3 Password')
--before deletion
select * from @password a inner join @user b
on a.keyvalue = b.keyvalue
select * into #deletedID from @user where keyvalue=1 -- this works like the output example
delete @user where keyvalue =1
delete @password where keyvalue in (select keyvalue from #deletedid)
--After deletion--
select * from @password a inner join @user b
on a.keyvalue = b.keyvalue
回答by kayode
All has been pointed out. Just use either DELETE ON CASCADEon the parent tableor delete from the child-tableto the parent.
都被指出了。只是要么使用DELETE ON CASCADE父table或从删除child-table的parent。
回答by Peter Perhá?
As Aaron has already pointed out, you can set delete behaviour to CASCADE and that will delete children records when a parent record is deleted. Unless you want some sort of other magic to happen (in which case points 2, 3 of Aaron's reply would be useful), I don't see why would you need to delete with inner joins.
正如 Aaron 已经指出的那样，您可以将删除行为设置为 CASCADE，这将在删除父记录时删除子记录。除非您希望发生某种其他魔法（在这种情况下，Aaron 回复的第 2、3 点会很有用），我不明白您为什么需要使用内部联接进行删除。
