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

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" <PrivateBenjamin_at_hushmail.com> wrote in message news:54df0379.0210280851.19f6c806_at_posting.google.com...
> 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
Received on Tue Oct 29 2002 - 08:31:50 CST