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

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#);

PrivateBenjamin_at_hushmail.com (April) 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 - 14:04:43 CST