sql - 在 Oracle 数据库中更新连接查询结果中每一行的列的最有效方法是什么？

Question

首先让我描述一下场景:

假设我有两个表 PRODUCTS 和 CUSTOMERS，它们可以描述如下:

产品表:

+----------+
| PRODUCTS |
+----------+
| ID       |
| CARD_ID  |
+----------+

和 CUSTOMERS 表:

+-----------+
| CUSTOMERS |
+-----------+
| ID        |
| APP_ID    |
| CARD_ID   |
+-----------+

所有这些字段都是数字类型。

这两个表相当大，已经包含了大量数据。在 CUSTOMERS 表中，CARD_ID 与 PRODUCTS 表中的 CARD_ID 相同。这些表之间是一对一的关系，即一个客户只能使用一个CARD_ID，不能有多个客户使用CARD_ID。但有些客户可能还没有使用任何产品，即 CARD_ID 为空。

我想在我们的 PRODUCTS 表中为 CUSTOMERS 表添加一个反向引用(为了应用程序)。所以我做了类似的事情:

ALTER TABLE PRODUCTS ADD CUSTOMER_ID NUMBER DEFAULT -1 NOT NULL;

现在，由于已有数据，我还需要编写一个迁移查询。也就是说，如果客户使用卡 ID C，那么我需要在 C 的客户 ID 字段中为所有此类现有匹配项添加该客户 ID。

我想过使用类似 this 的东西，但我的理解是，内部选择查询在这里是一个内部连接，所以我不确定在这种情况下是否有效。

我也考虑过使用“with”子句，但我认为“with”仅适用于选择查询。[更新]找到this使用“with”从句，但这是一个好方法吗？

帮我弄清楚应该如何有效地完成这项工作。例如，我不想更新 PRODUCTS 中目前未被任何客户使用的行。

数据库:Oracle 12c。

提前致谢。

[编辑]只是想提一下，我最终为此使用了 PL/SQL。在 PL/SQL 中这很容易。此外，我们更容易通过 flyway 进行处理迁移系统。

DECLARE

CURSOR C IS SELECT customer.APP_ID, customer.CARD_ID FROM CUSTOMERS customer INNER JOIN PRODUCTS prod on customer.CARD_ID = prod.CARD_ID;

BEGIN

FOR rec IN C
LOOP
    UPDATE PRODUCTS p SET p.CUSTOMER_ID = rec.APP_ID WHERE p.CUSTOMER_ID is null and p.CARD_ID = rec.CARD_ID;

END LOOP;

END;

只是完成任务的另一种方式。这适合我的特定应用。这是最好的方法吗？可能是，也可能不是!

Answer 1

我同意您应该避免更新产品 表。我什至不会向其中添加 customer_id 列:它不仅会使 customers.card_id 列的更新花费更多时间——因为它还需要更新products 表 - 如果实现不严密，甚至可能导致不一致。

相反，我建议创建一个数据库 View ，将 customer_id 与 products 列连接起来:

create view vw_products as
select      products.id, products.card_id, customers.customer_id
from        products
inner join  customers
        on  customers.card_id = products.card_id;

那样的话，您实际上并没有存储 customer_id，但可以从这个 View 中选择而不是原始的 products 表，得到你想要什么。此外，无需对表数据进行迁移。

即使数据量很大，如果您在两个表的 card_id 列上都定义了索引，这也会表现良好。

如果出于某种原因您确实需要将 customer_id 列添加到 products 表中，请按如下方式更新该新列:

UPDATE products
SET    customer_id = (
    SELECT customer_id
    FROM   customers
    WHERE  customers.card_id = products.card_id);

注意:这种数据库设计看起来有悖常理:客户和产品之间的一对一关系是这家企业破产的保证。