Re: Simple update query from joined tables

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 - 07:49:39 CDT