是否可以在同一个SQL语句中从多个表中删除?

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

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

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

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.

但是，我有兴趣删除连接条件的两边——连接表记录和它所依赖的用户记录。我不能打开cascade，因为我的解决方案首先是实体框架代码，双向关系可以创建多个cascade路径。

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?

从句法上来说这行不通，但我很好奇这样的事情是否可能?

5 个解决方案

#1

Nope, you'd need to run multiple statements.

不，你需要运行多个语句。

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

因为需要从两个表中删除，考虑创建匹配id的临时表:

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

And then delete from each of the tables:

然后从每个表中删除:

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

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

#2

The way you say is Possible in MY SQL but not for SQL SERVER

您所说的方式在我的SQL中是可能的，但在SQL SERVER中不是

You can use of the "deleted" pseudo table for deleting the values from Two Tables at a time like,

您可以使用“已删除”伪表一次从两个表中删除值，

 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;

For brief Explanation you can take a look at this Link

为了简单的解释，你可以看看这个链接

and to Know the Use of Deleted Table you can follow this Using the inserted and deleted Tables

要了解已删除表的使用，可以使用已插入和已删除的表进行跟踪

#3

The only way I could think of is logically break the bi-directional foreign keys in a procedural way.

我能想到的唯一方法是逻辑地以一种过程的方式打破双向外键。

This approach can have huge impact to your application side if you don't have some flags for visualization state or status

如果您没有一些显示状态或状态的标志，这种方法可能对您的应用程序方面产生巨大的影响。

Something like

类似的

INSERT dummy not visible rows to Users (with something like Id = -1 for dummy values)
向用户插入不可见的假行(假值的Id = -1)
Add to LinkingTable an alternative column to point back to Users, I'll call it U_ComesFrom

向LinkingTable添加指向用户的替代列，我将它命名为U_ComesFrom

ALTER TABLE LinkingTagble ADD U_ComesFrom_U_id INT DEFAULT(-1)

修改表链接添加U_ComesFrom_U_id默认值(-1)
Add FOREIGN KEY with a NOCHECK

添加外键与NOCHECK

ALTER TABLE LinkingTable WITH NOCHECK
FOREIGN KEY (U_ComesFrom_U_id)
REFERENCES Users (Id) ;

使用NOCHECK外键(U_ComesFrom_U_id)修改表链接表引用用户(Id);
Add to Users column

添加用户列

ALTER TABLE Users ADD MarkedForDeletion BIT NOT NULL DEFAULT(0)

修改表用户添加MarkedForDeletion位非空默认值(0)

Then your SQL would look like

那么您的SQL应该是这样的

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

This approach would impact the performance since each transaction would be at least 4 DML operations per bi-directional keys.

这种方法将影响性能，因为每一个事务将至少有4个DML操作每个双向键。

#4

Use TRY CATCH with Transaction

使用TRY CATCH with事务。

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

or you can also use Store procedure for same Using Stored Procedure With Transaction:

或者您也可以使用存储过程与事务一起使用存储过程:

#5

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

如果您正在通过T-SQL创建外键，您必须将ON DELETE CASCADE选项附加到外键:

Code Snippet 

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

ON DELETE CASCADE;

#1