Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Update multiple rows
dkolva_at_atlab.com (Don) wrote in message news:<7f205d5c.0212110732.7bbd6d76_at_posting.google.com>...
> Been working with Access and SQL Server for a long time. New to
> Oracle.
>
> I have two tables TableA & TableB.
> TableA has EmployeeID (PK),Salary, StartDate
> TableB has UserID, EmployeeID, Salary, StartDate (UserID,EmployeeID as
> PK)
> This allows multiple users to temporarily modify the salalaries in
> table B
>
> I then create a view:
> select EmployeeID,Salary from TableB where UserID=USER
>
> If I try to update the Salary and StartDates in TableA from the view,
> I receive the "ORA-01779: cannot modify a column which maps to a non
> key-preserved table"
>
> This is an upgrade from an existing schema, so rewritting the table
> structure is not an option. Is there a way to update the Salary and
> StartDate in TableA without using a cursor?
>
> Since the real example has about 12 fields that are updated, using
> single subqueries for each field would also be drag.
>
> Any help is appreciated.
The view you defined in your post doesn't even look at table A, but if you are trying to update from a view to a non-key preserved table try looking up instead of triggers.
HTH -- Mark D Powell -- Received on Wed Dec 11 2002 - 12:39:50 CST