sql - 我可以在一个语句中删除两个表中的条目吗？

Question

我必须从两个表中的每一个中删除一行，它们通过 ID 链接但没有正确的 PK - FK 关系(这个数据库没有外键!)

这些表具有假设的 1-1 关系。我不知道为什么不将它们放在同一张表中，但我无权更改它。

人

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

我找到了一个有希望的答案 here on 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) 中给出了语法错误，我也尝试过不使用别名，但似乎无法同时删除两个表

Answer 1

Can I delete entries from two tables in one statement?

没有。一条语句只能从 MS SQL Server 中的一个表中删除行。

你提到的关于 MySQL 和 MySQL 的答案确实允许用一条语句从多个表中删除，如 MySQL docs 中所示。 . MS SQL Server 不支持此功能，如 docs 中所示.在 SQL Server 的 DELETE 语句中没有包含多个表的语法。如果您尝试从 View 而不是表中删除，也有一个限制:

The view referenced by table_or_view_name must be updatable andreference exactly one base table in the FROM clause of the viewdefinition.

---

I was hoping to avoid two separate statements on the off-chance thesecond doesn't work for whatever reason, interrupted - concurrencyreally, I guess the TRY/CATCH will work well for that.

这就是交易的用途。您可以在一个事务中放置多个语句，它们要么都成功，要么都失败。全有或全无。

在您的情况下，您不仅可以，而且应该将两个 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

我强烈推荐阅读一篇很棒的文章 Error and Transaction Handling in SQL Server Erland Sommarskog 着。

---

如果你想变得狡猾，像这样:

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;

你会得到一个错误:

View or function 'CTE' is not updatable because the modificationaffects multiple base tables.

或者像这样:

WITH
CTE
AS
(
    SELECT
    PersonId
    FROM Info

    UNION ALL

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

你会得到另一个错误:

View 'CTE' is not updatable because the definition contains a UNIONoperator.