ORA-01779: cannot modify a column which maps to a non key-preserved table

[April]

Hi,

I am trying to update a field (employee#)in a table when the same
field (employee#) changes in another table using the sql below.

UPDATE (
SELECT
c.Employee# rel_empnum,
a.EMPLOYEE# inv_empnum
FROM
vw_tblInvestments a,
vw_tblSub_Investments b,
vw_TBLRELEASES c
WHERE
a.INVESTMENT# = b.INVESTMENT# AND
b.RELEASE# = c.RELEASE#)
SET rel_empnum = inv_empnum

the primary keys are:

Investment# - tblInvestments

Investment# - tblSub_Investments
Subinvestment# - tblSub_Investments
Release# - tblSub_Investments

Release# - tblReleases

* there could be multiple release# for each investments

why am I receiving the message "ORA-01779: cannot modify a column
which maps to a non key-preserved table" when all tables have primary
keys and are joined through those keys?

Or is there a different way to write the sql statement?

Thanks for your help
April

[Tom Best]

April:

A "key-preserved" table is one where every primary key or unique key value
in the base table is also unique in the join result. It doesn't matter if
you are selecting it, and joining on it. The important thing is that it
must still be unique in the result set.

Do the query and see if you get repeated values in the result set for
"tblReleases.Release#". If so, then it's not key-preserved.

HTH,
Tom Best

"April" <Private...@hushmail.com> wrote in message
news:54df0379.02102...@posting.google.com...

[D.Y.]

Oracle will not allow you to update a single row with values from
multiple rows. From the constraints on your tables Oracle decides
that your SQL could violate its rules.

If you want to update vw_tblreleases with any employee# you can find
in vw_tblinvestments which match your join criteria, try this,
update vw_tblreleases c
set c.employee#=(select a.employee# from vw_tblinvestments a,
vw_tblsub_investments b where a.investment#=b.investment# and
b.release#=c.release# and rownum<2)
where exists
(select 'x' from vw_tblinvestments a,vw_tblsub_investments b
where a.investment#=b.investment# and b.release#=c.release#);

Private...@hushmail.com (April) wrote in message news:<54df0379.02102...@posting.google.com>...

[srivenu]

You can use DBA_UPDATABLE_COLUMNS to see which columns can be updated,
deleted or inserted into.
regards
Srivenu