July 19, 2006

Updating Views

Last week I mentioned that there are at least three ways of updating a table from multiple source tables while avoiding the problem of putting NULL values into the table. These methods include repeating the SELECT clause as a WHERE EXISTS, using Oracle functions such as NVL, and today's solution: Updating through a view. (Once again, this column will use Oracle syntax. Other databases such as MS SQL Server don't really have this issue.)

What does it mean to "update a view"? After all, normal views don't really contain data—only the underlying tables contain the data. We can "update a view" if the view refers to one table. This is the same as updating the underlying data.

But we can also write a view that joins several tables and updates one of them. This makes it easy to gather data from several sources and update a target table while avoiding the NULL value problem. We don't even need to define the view in advance—it's done on the fly. The best way to see this is through an example.

  UPDATE (
  SELECT A.SOME_FIELD A_SOME_FIELD, B.SOME_FIELD B_SOME_FIELD
  FROM   PS_FIRST_TABLE   A
       , PS_SECOND_TABLE  B
  WHERE  B.EMPLID = A.EMPLID
  AND    ...
  )
  SET A_SOME_FIELD = B_SOME_FIELD

This example creates an unnamed temporary view and then updates one of the tables in the view (the one with an alias of "A" in this example). Note that even though we can refer to the fields using "A." and "B." within the view, those aliases are not visible outside of the view. Therefore, we must rename the fields as shown. (I used "A_fieldname" and "B_fieldname" for convenience. They could be called anything.) Then, outside of the view, the UPDATE refers to the fields by these temporary names. Setting A_SOME_FIELD to a value is then the same thing as updating field A.SOME_FIELD.

You know that when you join table 1 with table 2 you can get more rows than actually exist in table 1. When updating views using this technique, you must ensure that each row from the table being updated can appear no more than once in the view. Otherwise you will get an error that the table is not "key preserved". The way to do this is to use key values (MAX(EFFDT), for example) to ensure that no duplicates appear from the other tables. If you are using temporary tables, you will need to create a unique index on each one to let Oracle know that your selection on the key values will return only one row for each key value. Oracle wants to know that the updated table is key preserved before it will even try. This is not like inserting rows into a table, where Oracle tries the insert before complaining about duplicates. The reason for the restriction is that Oracle wants to know which value to use for the update; multiple rows could mean that multiple values should be placed into one field, which is a meaningless situation. So not only do you need to ensure that rows do not actually appear more than once, you must ensure that they cannot appear more than once.

Although the "key preserved" concept may be a little confusing at first, this technique can be useful in simplifying some types of updates.

Until next time...