在两个以上的表上使用内连接删除查询 [英] Delete Query using Inner joins on more than two tables

问题描述

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

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.

推荐答案

根据您的描述，我将架构设为:

From your description, I take the schema as:

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

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

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

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

C(pk_c, fk_A, col1, col2, ..., 外键 fk_A 引用 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, ..., 外键 fk_A 引用 A(pk_A))

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

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

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.

从表D中删除数据的三种情况:

There are three scenarios on deleting data from table D:

1. 您想从表 D 中删除数据，其中 fk_A(自然)使用列 A.pk_A 引用表 A 中的记录:

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

您想从表 D 中删除数据，其中 fk_A 引用了表 A 中的一条记录，而表 A 中的该记录也由列 B.fk_A 引用.我们不想从 D 中删除 A 中但 B 中没有的数据.我们可以这样写:

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

第三种情况是当我们必须删除表 D 中引用表 A 中的记录的数据时，并且 A 中的记录也被列 B.fk_A 和表 C.fk_A 引用.我们想只删除表 D 中的数据，这在所有四个表 - A、B、C 和 D 中都是通用的.我们可以这样写:

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 only that 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
);

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

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

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

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.

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

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.

这篇关于在两个以上的表上使用内连接删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！