Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: dkolva@atlab.com (Don)
Newsgroups: comp.databases.oracle.server
Subject: Re: Update multiple rows
Date: 12 Dec 2002 08:35:07 -0800
Organization: http://groups.google.com/
Lines: 44
Message-ID: <7f205d5c.0212120835.39dfe245@posting.google.com>
References: <7f205d5c.0212110732.7bbd6d76@posting.google.com> <2687bb95.0212111039.57b7e764@posting.google.com>
NNTP-Posting-Host: 66.26.16.223
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1039710907 5562 127.0.0.1 (12 Dec 2002 16:35:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 12 Dec 2002 16:35:07 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.server:169523
X-Received-Date: Thu, 12 Dec 2002 09:34:44 MST (news.easynews.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@eds.com (Mark D Powell) wrote in message news:<2687bb95.0212111039.57b7e764@posting.google.com>...
> dkolva@atlab.com (Don) wrote in message news:<7f205d5c.0212110732.7bbd6d76@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 --