| Updating Views | KEVIN RESCHENBERG
07-19-2006 |
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.
|
