Musings 1/15/2013

Alright, so today marks exactly 6 years to the day i landed in USA for first time!! Hurray some anniversary that. 

Updating one table from values of another

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:164612348068

3 good ways given here.

tkyte@ORACLE> update ( select a1, b1 from a, b where a.id = b.id )
  2     set a1 = b1
  3  /

1 row updated.


The following updates are equivalent as well - they do the same thing differently.  It is 
best to use the above update of a JOIN if possible (optimizer has best chance of getting 
a good plan).  It will need the primary key constraint on B though, if you do not have 
one BUT b.id is unique, you can:

tkyte@ORACLE> update a
  2     set a1 = ( select b1 from b where b.id = a.id )
  3   where EXISTS ( select b1 from b where b.id = a.id )
  4  /

1 row updated.

tkyte@ORACLE> update a
  2     set a1 = ( select b1 from b where b.id = a.id )
  3   where a.id in ( select id from b )
  4  / 

Before reading this page i encountered a Cannot modify a column that maps to a non-key preserved table

when trying using the first kind of join where table B did not have a key defined. So using either second or third technique will work in such situations