oracle 用另一列的值更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1331294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update column with values from another column
提问by OscarRyz
I have a table like this:
我有一张这样的表：
create table foo ( a number, b number )
I want to update all the columns from a with the value that is in another table
我想用另一个表中的值更新 a 中的所有列
create table bar ( x number, y number )
So, if this would be a procedural programing language I would:
因此，如果这将是一种过程式编程语言，我会：
foreach foo_item in foo
foreach bar_item in bar
if( foo_item.b == bar_item.y )
foo_item.a = bar_item.x
end
end
end
I have tried
我试过了
update foo
set a = ( select distinct( x ) from bar where bar.y = foo.b )
But it hangs.... I'm not really sure how to do such a thing ( or even what to google for )
但它挂起...... 我不确定如何做这样的事情（甚至不知道谷歌搜索什么）
Thanks
谢谢
EDITSorry my bad. It doesn't hang, but it tries to set va null value and I have a constraint ( which I can't remove )
编辑对不起我的错。它没有挂起，但它尝试设置 va null 值并且我有一个约束（我无法删除）
Thanks for the help so far
感谢你目前的帮助
采纳答案by borjab
update foo set b = ( select distinct( x ) from bar where bar.y = foo.b )
更新 foo set b = ( select distinct( x ) from bar where bar.y = foo.b )
May hang for performance reasons but should work. Double check what happends if there is no bar.y equal to foo.b. If it sets b to null is OK?
可能出于性能原因挂起，但应该可以工作。如果没有 bar.y 等于 foo.b，请仔细检查会发生什么。如果将 b 设置为 null 可以吗？
回答by Shannon Severance
There are two possible reasons for the update to attempting to foo.a to NULL.
尝试将 foo.a 更新为 NULL 有两个可能的原因。
- There exists rows in foo for which there is no matching rows in bar.
- The matching row in bar has bar.x of null.
- foo 中存在的行在 bar 中没有匹配的行。
- bar 中匹配的行的 bar.x 为 null。
The following will exclude updates to foo if either of the above conditions is true. In those cases foo.a will remain as it was:
如果上述任一条件为真，以下内容将排除对 foo 的更新。在这些情况下 foo.a 将保持原样：
update foo
set a = (select distinct( x ) from bar where bar.y = foo.b )
where exists
(select *
from bar
where bar.y = foo.b
and bar.x is not null);
回答by OMG Ponies
This fails/spins:
这失败/旋转：
UPDATE foo
SET b = (SELECT DISTINCT(x)
FROM bar
WHERE bar.y = foo.b)
...because you are updating the same value you want to use to determine what to update with. Oracle always allows a user to read data.
...因为您正在更新要用于确定更新内容的相同值。Oracle 始终允许用户读取数据。
回答by gapple
In the query you provided you seem to have a typo. In your procedural code you modify the value of foo.a, but your query updates foo.b:
在您提供的查询中，您似乎有一个错字。在您的程序代码中，您修改了 foo.a 的值，但您的查询更新了 foo.b：
update foo set a = ( select distinct( x ) from bar where bar.y = foo.b )
Also, if there are many rows with identical values for bar.y, problems may occur. Your subquery may return a result set, not a single value that your assignment expects. For example if your data is
此外，如果 bar.y 有许多具有相同值的行，则可能会出现问题。您的子查询可能会返回一个结果集，而不是您的赋值期望的单个值。例如，如果您的数据是
foo(x,y) = [{1,2},{2,2},{3,2}]
Then "DISTINCT x" will return '{1,2,3}'
然后 " DISTINCT x" 将返回 ' {1,2,3}'
回答by Christian13467
Assume you have following values.
假设您有以下值。
foo(a,b) = [{0,2}]
bar(x,y) = [{1,2},{2,2},{3,2}]
The answer given above raises an ORA-01427 error. The statement need some additions, which depend on the expected result.
If you expect that the greatest bar(x,2) should be stored in foo(a,2).
上面给出的答案引发了 ORA-01427 错误。该语句需要一些添加，这取决于预期的结果。
如果您希望最大的 bar(x,2) 应该存储在 foo(a,2) 中。
foo(a,b) = [{3,2}]
update foo
set a = (select max(x) from bar where bar.y = foo.b
and bar.x is not null)
where exists
(select *
from bar
where bar.y = foo.b
and bar.x is not null);
If you expect any value of bar(x,2) than write following.
如果您期望 bar(x,2) 的任何值，请编写以下内容。
foo(a,b) = [{[1|2|3],2}]
update foo
set a = (select x from bar where bar.y = foo.b
and bar.x is not null
and rownum < 2)
where exists
(select *
from bar
where bar.y = foo.b
and bar.x is not null);
The order of the subselect depends on storage and row retrival. Both updates can give the same result. Without an ORDER BY the roworder is not predictable. The rownum < 2takes only the first row of the subselect.
子选择的顺序取决于存储和行检索。两种更新都可以给出相同的结果。如果没有 ORDER BY，则行顺序是不可预测的。的ROWNUM <2只在当前子选择的第一行。
回答by jatanp
If you are using MS SQL Server or Sybase, you can use following,
如果您使用的是 MS SQL Server 或 Sybase，则可以使用以下命令，
update foo set b = x from bar where bar.y = foo.b
更新 foo set b = x from bar where bar.y = foo.b
Sorry, I did not see that you are using Oracle. I guess you would have to create Stored Procedure for that.
抱歉，我没有看到您正在使用 Oracle。我想你必须为此创建存储过程。
