sql - 更新单列的所有行

Question

我正在处理两个有 2 列的表，如下所列。

表 1:table_snapshot帐户号 |余额到期

表2:table_paid帐户号 |后平衡 | delta_balance

我使用以下命令向 table2 添加了第三列:

ALTER TABLE table_paid ADD delta_balance number(18);

我正在尝试使用以下查询，用 1 和 2 之间的余额差值更新新列 ( delta_balance )。仅供引用，table_paid 是 table_snapshot 的子集。即，表 2 仅包含表 1 中的几个帐户。我收到一条错误消息:SQL 语句未正确结束。我正在使用的查询是:

UPDATE table_paid
SET table_paid.delta_balance = table_paid.post_balance - table_snapshot.balance_due
from table_paid, table_snapshot
WHERE table_paid.account_no = table_snapshot.account_no;

如果有人可以纠正我的查询，我们将不胜感激。

非常感谢。

新手。

Answer 1

Oracle 没有您在 MS Sql Server 中使用的 UPDATE ... FROM 语法(我相信，无论如何它都不是 ANSI)。相反，当您需要对结果集进行更新时，Oracle 让您将结果集创建为一种内联 View ，然后通过 View 进行更新，如下所示:

  UPDATE ( SELECT tp.delta_balance
                , tp.post_balance
                , ts.balance_due
             FROM table_paid tp
                  JOIN table_snapshot ts
                    ON tp.account_no = ts.account_no
         )
     SET delta_balance = post_balance - balance_due;

这比 Babar 和 palindrom 提供的答案更“正确”，因为他们的查询将更新 table_paid 中的每一行，即使 table_snapshot 中没有相应的行。如果存在一一对应，则无需担心，但使用内联 View 进行更安全。

从您的示例中不清楚哪个表是父表，或者(据我猜测)都不是父表，并且 account_no 指向另一个表的主键(大概是帐户，或按您的命名为“table_account”)惯例)。无论如何，很明显你的表中不存在一一对应的关系——一个是 15K，另一个是数百万。

这可能意味着两件事:要么 table_snapshot 中有很多行在 table_paid 中没有对应的行，要么 table_snapshot 中的每一行都有很多行。如果后者为真，则您的查询是不可能的 - 您将对 table_paid 中的每一行进行多次更新，并且结果将是不可预测的；您如何知道哪个“post_balance -balance_due”表达式将最终确定给定的 delta_balance 的值？

如果您运行我的查询，您会很快发现这一点 - 您将收到一条错误消息，显示“ORA-01779:无法修改映射到非键保留表的列”。该错误的出现不是基于表中的数据(可能没问题)，而是基于您在两个表上定义的主键。如果您指定的联接条件没有明确导致更新的表与联接的其余部分之间基于定义的键形成 1-1 关系，您将收到此错误。这是 Oracle 告诉您的方式:“您将搞砸您的数据”。

在此处的其他答案中，如果您确实拥有会导致问题的数据，您只会收到错误(在这种情况下，ORA-01427:单行子查询返回多于一行)；我的版本更严格，因此您可能需要使用其他版本。

而且，正如其他人所说，您肯定需要在 account_no 上为 table_snapshot 表建立一个索引。 table 上的一份_付费也没什么坏处。