Doing a ‘join update’ in Oracle can sometime cause a few headaches. By ‘join update’ I mean the situation where we have two tables and want to update some of the rows in the first table based on values that exist in the second table.
Imagine this scenario :
drop table t;
drop table s;
create table t (id number, name varchar2(32));
create table s (id number, name varchar2(32));
insert into t values (1,'1');
insert into t values (2,'2');
insert into t values (3,'3');
insert into t values (4,'4');
insert into t values (5,'5');
insert into s values (3,'three');
insert into s values (5,'five');
select * from t;
update t
set name = (select name
from s
where t.id = s.id);
select * from t;
Our first table (t) simply has the numbers 1-5 in the ‘id’ and ‘name’ column but we want to update some of the names based on the contents of the second table (s) but this table only has values for two of the rows. This is OK so long as we can preserve the ‘default’ numeric values. However the script above gives the following output :
drop table t succeeded.
drop table s succeeded.
create table succeeded.
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
ID NAME
---------------------- --------------------------------
1 1
2 2
3 3
4 4
5 5
5 rows selected
5 rows updated
ID NAME
---------------------- --------------------------------
1
2
3 three
4
5 five
5 rows selected
The problem here is that the update isn’t a true ‘join’ as every row is going to be updated and if the ‘join’ fails then a null is written. The traditional solution to this is to rewrite the SQL to be something like :
update t
set name = (select name
from s
where t.id = s.id)
where exists (select 1
from s
where t.id = s.id);
select * from t;
which does the job correctly :
2 rows updated
ID NAME
---------------------- --------------------------------
1 1
2 2
3 three
4 4
5 five
5 rows selected
However this isn’t a very good solution for updates involving very large tables as it can significantly slow things down. One alternative is the MERGE statement :
merge into t
using (select id, name
from s) s
on (t.id = s.id)
when matched then
update set t.name = s.name;
select * from t;
2 rows merged
ID NAME
---------------------- --------------------------------
1 1
2 2
3 three
4 4
5 five
5 rows selected
An other option is to use the NVL() function which allows you to deal with the problem of the nulls above :
update t
set name = nvl((select name
from s
where t.id = s.id), t.name);
select * from t;
which also works :
5 rows updated
ID NAME
---------------------- --------------------------------
1 1
2 2
3 three
4 4
5 five
5 rows selected
