A Different Projection

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