oracle 更新单列的所有行

Update all rows of a single column

提问by novice

I'm dealing with two tables which have 2 columns, as listed under.

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

Table 1: table_snapshot account_no | balance_due

表 1：table_snapshot account_no | 余额到期

Table 2: table_ paid account_no | post_balance | delta_balance

表2：table_付费账户_no | post_balance | delta_balance

I added a third column to table2 with the following command:

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

ALTER TABLE table_paid ADD delta_balance number(18);

I'm trying to use the following query, to update the new column ( delta_balance ) with the difference in balances between 1 and 2. FYI, table_paid is a subset of table_snapshot. i,e., table 2 has only a few accounts present in table 1. I get an error saying : SQL Statement not properly ended. the query i'm using is:

我正在尝试使用以下查询，以 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;

Appreciate if someone can correct my query.

感谢有人可以纠正我的查询。

Many thanks.

非常感谢。

novice.

新手。

回答by Babar

Try this

尝试这个

 UPDATE table_paid
 SET table_paid.delta_balance = table_paid.post_balance - 
 (SELECT table_snapshot.balance_due from table_snapshot WHERE table_paid.account_no = 
 table_snapshot.account_no);

回答by Steve Broberg

Oracle doesn't have the UPDATE ... FROM syntax that you're using from MS Sql Server (which, I believe, isn't ANSI anyway). Instead, when you need to do an update on a result set, Oracle has you create the resultset as a kind of inline view, then you update through the view, like so:

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

  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;

This is more "correct" than the answers supplied by Babar and palindrom, as their queries will update every row in table_paid, even if there are no corresponding rows in table_snapshot. If there is a 1-1 correspondance, you don't need to worry, but it's safer to do it with the inline view.

这比 Babar 和 palindrom 提供的答案更“正确”，因为他们的查询将更新 table_paid 中的每一行，即使 table_snapshot 中没有相应的行。如果有 1-1 的对应关系，您不必担心，但使用内联视图进行更安全。

It's unclear from your example which table is the parent table, or (as I'm guessing) neither is the parent table and account_no is pointing to the primary key of another table (presumably account, or "table_account" by your naming conventions). In any case, it's clear that there is not a 1-1 correspondence in your table - 15K in one, millions in the other.

从您的示例中不清楚哪个表是父表，或者（正如我猜测的）父表和 account_no 都不是指向另一个表的主键（根据您的命名约定，大概是 account 或“table_account”）。无论如何，很明显，您的表格中没有 1-1 对应关系 - 一个是 15K，另一个是数百万。

This could mean 2 things: either there are many rows in table_snapshot that have no corresponding row in table_paid, or there are many rows in table_snapshot for each row in table_paid. If the latter is true, your query is impossible - you will have multiple updates for each row in table_paid, and the result will be unpredictable; how will you know which of the "post_balance - balance_due" expressions will ultimately determine the value of a given delta_balance?

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

If you run my query, you will find this out quickly enough - you will get an error message that says, "ORA-01779: cannot modify a column which maps to a non key-preserved table". This error will appear based not on the data in the table (it may be okay), but based on the primary keys you have defined on the two tables. If the join condition you specify doesn't unambiguously result in a 1-1 relationship between the updated table and the rest of the join, based on the defined keys, you will get this error. It's Oracle's way of telling you, "You're about to screw up your data".

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

In the other answers here, you will only get an error (in that case, ORA-01427: single-row subquery returns more than one row) if you actually have data that would cause a problem; my version is more strict, so it may turn out that you will need to use the other versions.

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

And, as the others have said, you'll definitely want an index on account_no for the table_snapshot table. One on the table_paid wouldn't hurt either.

而且，正如其他人所说，您肯定希望 table_snapshot 表的 account_no 上有一个索引。table_paid 上的一个也不会受到伤害。

回答by palindrom

UPDATE table_paid SET table_paid.delta_balance = table_paid.post_balance - ( select balance_due from table_snapshot WHERE table_paid.account_no = table_snapshot.account_no )

UPDATE table_paid SET table_paid.delta_balance = table_paid.post_balance -（从 table_snapshot 中选择 balance_due table_paid.account_no = table_snapshot.account_no）