Update Selected Values from a Look-Up Table

This is a quick little example to demonstrate how to (and how not to) update selected values in a table from multiple values stored in a look-up table. First, let's create a couple of tables. The first will be our main table that we need to update. The second will be a list of various keys (the NM column) that have different values that need to be updated in the main table:

    
   drop table main_data ;

   drop table new_values ;

   create table main_data as 
     select  null  as nm
          ,  null  as val  
       from  dual  where 1=0
     union all  select  'bob', 13  from dual
     union all  select  'sam', 14  from dual
     union all  select  'joe', 15  from dual
     union all  select  'jan', 16  from dual
     union all  select  'fay', 17  from dual
     union all  select  'liz', 18  from dual ;
   
   create table new_values as 
     select  null  as nm
          ,  null  as val
       from  dual  where 1=0
     union all  select  'sam', 2345  from dual
     union all  select  'liz', 7865  from dual ;

As we can see, we have two new values that need to replace the values (in the VAL column) of our MAIN_DATA table.

    
     select * from main_data ;

     >  NM   VAL
     >  ---  ---
     >  bob   13 
     >  sam   14 
     >  joe   15 
     >  jan   16 
     >  fay   17 
     >  liz   18 


     select * from new_values ;

     >  NM    VAL
     >  ---  ----
     >  sam  2345 
     >  liz  7865 

We would like to run one UPDATE statement that will use the NEW_VALUES table to find the corresponding values for "sam" and "liz" and replace the existing "14" and "18" values in our main table.

So here is our first attempt. We'll update our table by setting the value equal to a correlated sub-query. The WHERE clause correlates the main table alias "a" with the sub-query's alias "b". When the names match, we want the VAL column from our NEW_VALUES table to update the VAL column in our MAIN_DATA table.

    
   update  main_data a
      set  val = ( select  val 
                     from  new_values b
                    where  b.nm = a.nm
                 ) ;

Now we can look at the data to see what happened:

    
     select * from main_data ;

     >  NM    VAL
     >  ---  ----
     >  bob        
     >  sam  2345 
     >  joe        
     >  jan        
     >  fay        
     >  liz  7865 

Noticed how badly we failed. The two values were updated, but all the other values were set to NULL. This is because our UPDATE statement didn't have a WHERE clause -- it updated every value in the table. Since it didn't find values for all the other names... it set them all to NULL.

Now let's look at a proper update statement. Notice that this one has two correlated sub-queries -- both looking at the same NEW_VALUES table. This time we will only update the rows WHERE a look-up value "exists". In this second sub-query notice that we are selecting a zero -- any constant value will do (you can even put NULL there). The WHERE clause is just verifying that a row exists in the look-up table (exists meaning that the "where b.nm = a.nm" evaluates to true). We "rollback" the previous update and run the new one:

    
   rollback ;
   
   update  main_data a
      set  val = 
             ( select  val 
                 from  new_values b
                where  b.nm = a.nm
             ) 
    where  exists 
             ( select  0
                 from  new_values b
                where   b.nm = a.nm
             ) ;

This time we get the result that we wanted. Update the values that exist in the look-up table, and leave all the other values alone.

    
     select * from main_data ;
     
     >  NM    VAL
     >  ---  ----
     >  bob    13  
     >  sam  2345 
     >  joe    15  
     >  jan    16  
     >  fay    17  
     >  liz  7865 

My preferred way of doing this kind of update is the following -- it's updating a sub-query or an inline view. In this case you MUST have a unique constraint on the key of the the look-up table -- here, I've added a primary key which fills that requirement.

Notice that the two tables are joined on the proper key, and I select  both the target value and the look-up value. Then, outside the sub-query  we can SET the target value to the new look-up value (using the column aliases). Notice that only the two values will be set (no "where exists" clause is needed) because the JOIN only returns the two rows that have keys in both tables.

    
   rollback ;

   alter table new_values 
     add constraint new_values_pk 
     primary key (nm) ;
   
   update
     ( select  a.val as target_val
            ,  b.val as lookup_val
         from  main_data   a
         join  new_values  b  on ( a.nm = b.nm )
     )    
     set target_val = lookup_val ;