我可以在一条语句中从两个表中删除记录吗？

Question

我可以在一条语句中从两个表中删除记录吗？

sqlsql-serversql-server-2012

3

我需要从两个表中删除一行记录，它们通过ID关联，但不存在适当的主外键关系（这个数据库没有外键！）。

这两个表应该是一对一的关系。我不知道为什么它们没有被放在同一个表中，但我不能更改它。

人员

PersonId | Name | OwnsMonkey
----------------------------
    1       Jim       true
    2       Jim       false
    3       Gaz       true

信息

PersonId |    FurtherInfo
-----------------------------
    1       Hates his monkey
    2        Wants a monkey
    3       Loves his monkey

为了决定删除什么，我需要找到一个用户名以及他们是否拥有一只猴子。

Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false'

因此，我使用这个思路进行了两次单独的语句，首先从 Info 中删除，然后再从 People 中删除。

delete from Info where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

delete from People where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

我在StackOverflow上找到了一个有前途的答案：这里。

delete      a.*, b.*
from        People a
inner join  Info b
where       a.People = b.Info
            and a.PersonId = 
            (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false')

但在 Sql Server (2012) 中会产生语法错误，我尝试了不使用别名的情况，但好像无法同时删除两个表。

- jamheadart

1

这个数据库没有外键！我很同情你。 - Dai

1

此外，您的双重“DELETE FROM”语句应该被包含在一个“TRY/CATCH”和“BEGIN TRANSACTION/COMMIT”中（在“CATCH”块中使用“ROLLBACK”）。 - Dai

2

一个DML语句一次只能影响一个对象。如果你需要影响多个表，则需要发出多个DDL语句（在这种情况下是“DELETE”），或者实现启用了“CASCADE”的主键和外键。 - Thom A

@ Dai，错误出现在第一个逗号处 Incorrect syntax near ','。 - jamheadart

2

@jamheadart TRY/CATCH 是必要的，但不足以保证数据完整性。你需要将其与 BEGIN TRANSACTION + COMMIT 结合使用（在 CATCH 块中使用 ROLLBACK）。 - Dai

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vladimir Baranov · Accepted Answer

我能否在一条语句中从两张表中删除条目？

不行。在MS SQL Server中，一条语句只能从一个表中删除行。你所指的答案谈到了MySQL，MySQL确实允许使用一条语句从多个表中删除，可以在MySQL文档中查看。而MS SQL Server不支持此功能，可以在文档中查看。在SQL Server中，DELETE语句中没有包括多个表的语法。如果您尝试从视图而不是表中进行删除，则也存在限制：

引用table_or_view_name的视图必须是可更新的，并且在视图定义的FROM子句中引用正好一个基本表。

我希望避免两个单独的语句，以防第二个由于任何原因而不能工作 - 并发吧，我想TRY/CATCH将对此很有用。

这就是事务用来做的事情。您可以将多个语句放在一个事务中，要么全部成功，要么全部失败。全部还是没有。在您的情况下，您不仅可以，而且应该将两个DELETE语句放在一个事务中。 TRY/CATCH帮助以更可控的方式处理可能的错误，但主要概念是"事务"。

BEGIN TRANSACTION

delete from Info where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

delete from People where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

COMMIT

我强烈推荐阅读Erland Sommarskog的文章《SQL Server中的错误和事务处理》。

如果你尝试这样做，就像这样：

WITH
CTE
AS
(
    SELECT
        Info.PersonId AS ID1, People.PersonId AS ID2
    FROM
        Info
        INNER JOIN People ON Info.PersonId = People.PersonId
)
DELETE FROM CTE
WHERE ID1 = 1;

你会收到一个错误：

视图或函数“CTE”无法更新，因为修改影响了多个基表。

或者像这样：

WITH
CTE
AS
(
    SELECT
    PersonId
    FROM Info

    UNION ALL

    SELECT
    PersonId
    FROM People
)
DELETE FROM CTE
WHERE PersonId = 1;

你将会得到另一个错误：

视图“CTE”不可更新，因为定义包含 UNION 运算符。