It’s a common requirement that we will have to update a table using values from another table.
Something like
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
Using above techinque we must remember that all the rows in T which did not have a match in S, will be updated to NULL. Which is not desired.
Hence we need to use the below technique:
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
where exists (select 1 from s s1 where t.key = s1.key_to_t and s1.fld_name='A');
By this we make sure that only the rows which match the criteria is updated and remaining are not touched.
But if the table is huge , the where exists clause may slowed down the query.
We can also use another method:
create table t ( key int, value int );
create table s ( key_to_t int, value int, fld_name varchar2(1), unique(value,fld_name) );
insert into t values ( 1, null );
insert into t values ( 2, 111 );
insert into s values ( 1, 100, 'A' );
insert into s values ( 1, 100, 'B' );
update (select t.value,s.value new_value
from t, s
where t.key = s.key_to_t
and s.fld_name = 'A')
set value = new_value;
For the above technique we must make sure we have a Primary Key column or Unique Index on the columns.
or
merge into t
using (select * from s where fld_name = 'A') s
on (t.key = s.key_to_t)
when matched then update set value = s.value;
