SQL 在两个以上的表上使用内部联接删除查询

Delete Query using Inner joins on more than two tables

提问by Sidd

I want to delete records from a table using inner joins on more than two tables. Say if I have tables A,B,C,D with A's pk shared in all other mentioned tables. Then how to write a delete query to delete records from table D using inner joins on table B and A since the conditions are fetched from these two tables. I need this query from DB2 perspective. I am not using IN clause or EXISTS because of their limitations.

我想在两个以上的表上使用内部联接从表中删除记录。假设我有表 A、B、C、D，其中 A 的 pk 在所有其他提到的表中共享。然后如何编写删除查询以使用表 B 和 A 上的内部联接从表 D 中删除记录，因为条件是从这两个表中获取的。我需要从 DB2 的角度来看这个查询。由于它们的局限性，我没有使用 IN 子句或 EXISTS。

回答by Rachcha

From your description, I take the schema as:

根据您的描述，我将架构视为：

A(pk_A, col1, col2, ...)

B(pk_B, fk_A, col1, col2, ..., foreign key fk_A references A(pk_A))

C(pk_c, fk_A, col1, col2, ..., foreign key fk_A references A(pk_A))

D(pk_d, fk_A, col1, col2, ..., foreign key fk_A references A(pk_A))

A(pk_A, col1, col2, ...)

B(pk_B, fk_A, col1, col2, ..., 外键 fk_A 引用 A(pk_A))

C(pk_c, fk_A, col1, col2, ..., 外键 fk_A 引用 A(pk_A))

D(pk_d, fk_A, col1, col2, ..., 外键 fk_A 引用 A(pk_A))

As you say DB2 will allow only 1000 rows to be deleted if IN clause is used. I don't know about DB2, but Oracle allows only 1000 manual values inside the IN clause. There is not such limit on subquery results in Oracle at least. EXISTS should not be a problem as any database, including Oracle and DB2 checks only for existence of rows, be it one or a million.

正如您所说，如果使用 IN 子句，DB2 将只允许删除 1000 行。我不了解 DB2，但 Oracle 只允许在 IN 子句中使用 1000 个手动值。至少在 Oracle 中对子查询结果没有这样的限制。EXISTS 不应该是任何数据库的问题，包括 Oracle 和 DB2 只检查行是否存在，无论是一百万还是一百万。

There are three scenarios on deleting data from table D:

从表 D 中删除数据有三种情况：

1. You want to delete data from table D in which fk_A (naturally) refers to a record in table A using column A.pk_A:

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
           WHERE a.pk_A = d.fk_A
   );
   

2. You want to delete data from table D in which fk_A refers to a record in table A, and that record in table A is also referred to by column B.fk_A. We do not want to delete the data from D that is in A but not in B. We can write:

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
      INNER JOIN b ON a.pk_A = b.fk_A
           WHERE a.pk_A = d.fk_A
   );
   

3. The third scenario is when we have to delete data in table D that refers to a record in table A, and that record in A is also referred by columns B.fk_A and table C.fk_A. We want to delete onlythat data from table D which is common in all the four tables - A, B, C and D. We can write:

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
      INNER JOIN b ON a.pk_A = b.fk_A
      INNER JOIN c ON a.pk_A = c.fk_A
           WHERE a.pk_A = d.fk_A
   );
   

1. 您想从表 D 中删除数据，其中 fk_A（自然地）使用 A.pk_A 列引用表 A 中的记录：

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
           WHERE a.pk_A = d.fk_A
   );
   

2. 您想从表 D 中删除数据，其中 fk_A 引用表 A 中的记录，而表 A 中的记录也被列 B.fk_A 引用。我们不想从 D 中删除 A 中但不在 B 中的数据。我们可以这样写：

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
      INNER JOIN b ON a.pk_A = b.fk_A
           WHERE a.pk_A = d.fk_A
   );
   

3. 第三种情况是当我们必须删除表 D 中引用表 A 中的记录的数据时，A 中的记录也被列 B.fk_A 和表 C.fk_A 引用。我们想删除只从表d数据是在所有的四个表常见- A，B，C和D，我们可以这样写：

   DELETE FROM d
   WHERE EXISTS (
          SELECT 1
            FROM a
      INNER JOIN b ON a.pk_A = b.fk_A
      INNER JOIN c ON a.pk_A = c.fk_A
           WHERE a.pk_A = d.fk_A
   );
   

Depending upon your requirement you can incorporate one of these queries.

根据您的要求，您可以合并这些查询之一。

Note that "=" operator would return an error if the subquery retrieves more than one line. Also, I don't know if DB2 supports ANY or ALL keywords, hence I used a simple but powerful EXISTS keyword which performs faster than IN, ANY and ALL.

请注意，如果子查询检索多于一行，“=”运算符将返回错误。另外，我不知道 DB2 是否支持 ANY 或 ALL 关键字，因此我使用了一个简单但功能强大的 EXISTS 关键字，它的执行速度比 IN、ANY 和 ALL 快。

Also, you can observe here that the subqueries inside the EXISTS clause use "SELECT 1", not "SELECT a.pk" or some other column. This is because EXISTS, in any database, looks for only existence of rows, not for any particular values inside the columns.

此外，您可以在此处观察到 EXISTS 子句中的子查询使用“SELECT 1”，而不是“SELECT a.pk”或其他一些列。这是因为 EXISTS 在任何数据库中只查找行的存在，而不查找列中的任何特定值。

回答by Vlad Bezden

Based on 'Using SQL to delete rows from a table using INNER JOIN to another table'

基于“使用 SQL 使用 INNER JOIN 从表中删除行到另一个表”

The key is that you specify the name of the table to be deleted from as the SELECT. So, the JOIN and WHERE do the selection and limiting, while the DELETE does the deleting. You're not limited to just one table, though. If you have a many-to-many relationship (for instance, Magazines and Subscribers, joined by a Subscription) and you're removing a Subscriber, you need to remove any potential records from the join model as well.

关键是您指定要从中删除的表的名称作为 SELECT。因此，JOIN 和 WHERE 进行选择和限制，而 DELETE 进行删除。但是，您不仅限于一张桌子。如果您具有多对多关系（例如，杂志和订阅者，通过订阅加入）并且您要删除订阅者，则还需要从联接模型中删除任何潜在记录。

DELETE subscribers
     FROM subscribers INNER JOIN subscriptions 
       ON subscribers.id = subscriptions.subscriber_id
     INNER JOIN magazines 
       ON subscriptions.magazine_id = magazines.id
     WHERE subscribers.name='Wes';

回答by Shamis Shukoor

delete from D 
where fk  = (select d.fk from D d,A a,B b where a.pk = b.fk and b.fk = d.fk )

this should work

这应该有效