October 2009 Entries

I’ve had a lot of feedback on my Oracle Update with Join post.  The most common problem people have encountered with this approach is the dreaded “SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table”.  Tom Kyte has a detailed explanation of this at Ask Tom.  However, I thought I would take a moment to address my example scenario.

There are actually a couple of ways to update based on data joined from multiple tables.  The first is outlined in my first post.  But, let’s dig a little deeper into that approach.  The “key-preserved” problem comes from joining to a table without some sort of unique lookup into that table.  So, the result of the join would not guarantee zero or one entries for each row I want to update.

I’m going to add a little to my test scenario.  Instead of setting the bonus to a constant value, I am going to set it to a value from a bonus rates table.  Let’s suppose we have:

   1: drop table employees cascade constraints;
   2: drop table employee_bonus cascade constraints;
   3: drop table bonus_rates cascade constraints;
   5: create table employees
   6: ( employee_id int primary key,
   7:   bonus_eligible char(1),
   8:   rate_id int
   9: )
  10: /
  12: create table employee_bonus
  13: ( bonus_id int primary key,
  14:   employee_id int,
  15:   bonus_date date,
  16:   bonus number
  17: )
  18: /
  20: create table bonus_rates
  21: ( rate_id int primary key,
  22:   min_bonus number,
  23:   max_bonus number
  24: )
  25: /
  27: insert into employees values ( 100, 'N', 101 );
  28: insert into employees values ( 200, 'Y', 102 );
  29: insert into employee_bonus values ( 101, 100, '15-DEC-09', 0 );
  30: insert into employee_bonus values ( 102, 200, '15-DEC-09', 2000 );
  31: insert into bonus_rates values ( 101, 0, 0 );
  32: insert into bonus_rates values ( 102, 500, 2000 );
  33: commit;

Now let’s update the employee_bonus table by copying the minimum bonus from the employees table:

   1: update (select b.bonus as bonus,
   2:                br.min_bonus as new_bonus
   3:           from employee_bonus b
   4:          inner join employees e on b.employee_id = e.employee_id
   5:          inner join bonus_rates br on e.rate_id = br.rate_id
   6:          where e.bonus_eligible = 'Y' ) t
   7: set t.bonus = t.new_bonus

The query runs just fine and updates the employee_bonus record for the "eligible” employee.  The reason it works is because to update the employee_bonus table, I am looking up the employee info based on a unique index (primary key in this case), and Oracle is certain that only one employee record will be returned given the employee_bonus record.

Just for grins, here is the other approach for updating with a join.  Keep in mind, though that the approach above should be your first try.

   1: update employee_bonus b
   2:    set b.bonus = (select br.min_bonus
   3:                     from employees e
   4:                    inner join bonus_rates br on e.rate_id = br.rate_id
   5:                    where b.employee_id = e.employee_id
   6:                      and e.bonus_eligible = 'Y' )
   7: where exists (select e.employee_id
   8:                 from employees e 
   9:                where b.employee_id = e.employee_id
  10:                  and e.bonus_eligible = 'Y' )

By the way, we are using a correlated sub query in this case.  For a correlated query, any join to the outer table, must be done in the where clause and not within the “inner join … on” syntax.  Otherwise you will get an “ORA-00904 … invalid identifier”.

Okay, so that’s it for a scenario that works fine because the “lookup” table (employees) is key-preserved.  So what about a scenario where the lookup table is not key preserved?  Let’s suppose the employee table has a bonus field that grabs the bonus from the employee_bonus table.  (Please keep in mind that this whole scenario is totally contrived, so I may be doing some things here that I wouldn’t normally.)

First, we need to add the bonus column:

   1: drop table employees cascade constraints;
   2: create table employees
   3: ( employee_id int primary key,
   4:   bonus_eligible char(1),
   5:   rate_id int,
   6:   bonus number
   7: )
   8: /
  10: insert into employees values ( 100, 'N', 101, null );
  11: insert into employees values ( 200, 'Y', 102, null );
  13: commit;

Now, let’s try to update the employees.bonus from the employee_bonus table:

   1: update (select e.bonus,
   2:                b.bonus as new_bonus
   3:           from employees e
   4:          inner join employee_bonus b on b.employee_id = e.employee_id) t
   5:    set t.bonus = new_bonus

Oops, that didn’t work:


Again, the problem is that there is no unique constraint on the lookup table (in this case employee_bonus) by the key we are joining to (employee_id).  So we could resolve the error by adding a unique constraint to the employee_id column.  However, there could be any number of reasons why we can’t.  Perhaps it is a “legacy” table/database that we have no control over.  Perhaps, there really is no guarantee that there would be only one entry per employee.  The bonus_date certainly seems to imply that.

Let’s take a look at the second approach:

   1: update employees e
   2:    set e.bonus = (select b.bonus
   3:                     from employee_bonus b
   4:                    where b.employee_id = e.employee_id)
   5: where exists (select b.employee_id
   6:                 from employee_bonus b
   7:                where b.employee_id = e.employee_id)

Actually this approach works fine with our given data.  However, if we had more data and there were an employee with more than one bonus, the query would fail.  Let’s see…

   1: insert into employee_bonus values ( 103, 200, '15-DEC-08', 1000 );
   3: commit;

and now if we run the same update statement:


I believe that older versions of Oracle would not produce this error, they would simply update the employee record twice, yielding unpredictable results.  I’m using 10g in my tests.

Let’s review what we’ve seen so far.  Basically, no matter how I cut it, the update I am attempting is flawed.  I’m trying to update one table data from another that is not guaranteed to be unique.  Maybe we “know” it always will be unique, but it would be best if we go ahead and add the necessary constraint.  In my scenario, there is something obviously wrong with my logic.  Perhaps, I was trying to get the total bonus or the max bonus.  Then, updating the employee table might make more sense.  But even updating from an aggregate sub query has its quirks.

Another scenario you may encounter is updating based on a join to a view.  It is highly unlikely that you will be able to “guarantee” (from Oracle’s perspective) uniqueness from the view.  The second approach is probably your only option in this case.

Anyway, I hope that I’ve gone into enough detail to help those who have been having trouble implementing the approach from my original post.  I’ll update this one if other common scenarios pop up.

Happy updating.

Tags: ,