Re: Simple update query from joined tables

Tim -

Check out my response to you at P2P.

Tom

Tim C wrote:

> Ok, ok, I'll make good on the $10 Niall, but I need an address - please
> email me.
>
> I guess I had reduced my original query a bit too much though. The real
> problem I had is that I wanted to update a table using a not-very complex
> select statement as source to an update. Oracle, however refused with the
> error: "ORA-01779: cannot modify a column which maps to a non key-preserved
> table"
>
> I think I understand that what the db is complaining about is that I have
> created a temporary view (via the select) from multiple tables, and it
> doesn't allow updates to all fields in the view - even though that's not
> what I intend to do.
>
> What I really want to do is define a set of rows via the select, then update
> any table I please based on key matches between those selected rows and the
> table to be updated. This is my problem.
>
> I did manage to write a script to process my source rows, one at a time &
> update selected rows in the target table, but this seems so in-elegant and
> unnecessary.
>
> I'm no expert, but I'm quite sure that I have performed this type of update
> before with some of Microsoft's products.
>
> "Tim C" <NOTCornwell_at_NOTcs.NOTcornell.NOTedu> wrote in message
> news:aic468$si0$1_at_news01.cit.cornell.edu...
> > $10 to you - if you think you can solve it.
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:l7uikuc81pflpgeaou5nd1e7a94cpiga40_at_4ax.com...
> > > On Thu, 1 Aug 2002 12:56:11 -0400, "Cornell news"
> > > <cornwell_at_clarityconnect.com> wrote:
> > >
> > > >I have had a very difficult time trying to figure this out:
> > > >
> > > >How do I update a table value from another table with matching keys in
> a
> > > >single UPDATE statement.
> > > >
> > > >Here's a scenario:
> > > >
> > > > Table_A has string fields String_1, and String_2
> > > > Table_B has fields String_3
> > > > Both tables have key fields key1
> > > >
> > > >Write an Update statement that concatenates the values of String_1 and
> > > >String_3 - and places the result in String_2 field for matching (by
> Key1)
> > > >rows in Table_A.
> > > >
> > > >So, for every matching Table_B row, update Table_A.String_2 field with
> > the
> > > >(Table_A.String_1||Table_B.String_3) value.
> > > >
> > > >
> > > >TIA,
> > > >Tim C.
> > > >
> > > >
> > > >
> > >
> > > How much do you pay for making your homework assignments?
> > >
> > > Regards
> > >
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> >
Received on Fri Aug 02 2002 - 12:03:00 CDT