Update column with values from another column

Question

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

Answer 1

If you are using MS SQL Server or Sybase, you can use following,

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

Answer 2

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?

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

Answer 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}'

Answer 5

There are two possible reasons for the update to attempting to foo.a to NULL.

1. There exists rows in foo for which there is no matching rows in bar.
2. 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);

Answer 6

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.