Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Update multiple rows

Re: Update multiple rows

From: Don <dkolva_at_atlab.com>
Date: 12 Dec 2002 08:35:07 -0800
Message-ID: <7f205d5c.0212120835.39dfe245@posting.google.com>


Mark,

Acutally, I solved this with the following:

UPDATE TableA A
SET (Salary, StartDate) =
(SELECT Salary, StartDate FROM TableB B WHERE A.EmployeeID=B.EmployeeID AND B.UserID=USER)

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0212111039.57b7e764_at_posting.google.com>...
> 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 Thu Dec 12 2002 - 10:35:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US