是否可以从同一SQL语句中的多个表中删除？ [英] Is it possible to delete from multiple tables in the same SQL statement?

问题描述

可以使用join语句删除要删除的集合，例如：

It's possible to delete using join statements to qualify the set to be deleted, such as the following:

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')

但是我有兴趣删除连接条件的两侧- LinkingTable 记录和它所依赖的用户记录。我无法打开级联，因为我的解决方案是首先使用实体​​框架代码，并且双向关系形成了多个级联路径。

However I'm interested in deleting both sides of the join criteria -- both the LinkingTable record and the User record on which it depends. I can't turn cascades on because my solution is Entity Framework code first and the bidirectional relationships make for multiple cascade paths.

理想情况下，我想要类似以下内容：

Ideally, I'd like something like:

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

从语法上讲这是行不通的，但是我很好奇

Syntactically this doesn't work out, but I'm curious if something like this is possible?

推荐答案

不是，您需要运行多个语句。

Nope, you'd need to run multiple statements.

由于需要从两个表中删除，因此考虑创建一个具有匹配ID的临时表：

Because you need to delete from two tables, consider creating a temp table of the matching ids:

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

然后从每个表中删除：

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

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

这篇关于是否可以从同一SQL语句中的多个表中删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！