更新单个列的所有行

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_ paid account_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:

我正在尝试使用以下查询来更新新列(delta_balance),其余值在1和2之间.FYI,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.

3 个解决方案

#1

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上(通过您的命名惯例推测帐户,或“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关系,则基于定义的键,您将收到此错误。这是甲骨文告诉你的方式,“你将搞砸你的数据”。

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上的一个也不会受到伤害。

#2

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

#3

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 WHERE table_paid.account_no = table_snapshot.account_no)

#1

  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;