可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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

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

EDIT Sorry my bad. It doesn't hang, but it tries to set va null value and I have a constraint ( which I can't remove )

Thanks for the help so far

回答1:

update 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?

回答2:

There are two possible reasons for the update to attempting to foo.a to 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.

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:

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

回答3:

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.

回答4:

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:

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

foo(x,y) = [{1,2},{2,2},{3,2}]

Then "DISTINCT x" will return '{1,2,3}'

回答5:

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

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.

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 < 2 takes only the first row of the subselect.