Re: SQL Problem

And I just copied it incorrectly as well

>>where m.nr = x.nr
>which I presume should read
> a.nr = x.nr

I meant:

    a.nr = m.nr

!!

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Jonathan Lewis wrote in message
<1013442450.17518.0.nnrp-02.9e984b29_at_news.demon.co.uk>...


>

>I assume that in copy the SQL to describe

>your problem you have (a) simplified it and

>(b) introduced a typing error.

>

>The problem line is:

>

>>where      m.nr = x.nr

>which I presume should read

>    a.nr = x.nr

>

>Since you are updating table Y

>(aliased by 'a'), then it is necessary

>that the join to table Z (aliased by 'm')

>should be using the primary key (or

>a unique key) on Z so that Oracle knows

>that the join will not introduce multiple

>copies of a row in table Y.  This is

>the essence of 'key-preservation'.

>

>--

>Jonathan Lewis

>http://www.jlcomp.demon.co.uk

>

>Now running 3-day intensive seminars

>http://www.jlcomp.demon.co.uk/seminar.html

>

>Host to The Co-Operative Oracle Users' FAQ

>http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

>Author of:

>Practical Oracle 8i: Building Efficient Databases

>

>

>Carsten Bliessen wrote in message <3c677d44.1983482_at_News.CIS.DFN.DE>...

>>Hi together,

>>

>>I have a little problem with an SQL Script. I need it for an Oracle 8

>>DB wich was installed from our Software company wich supports us. Now

>>I must change some data for myselfn and construct this script. But it

>>dosnīt work and I canīt find related information on the Internet - of

>>course I can find the Failure Code and some explanaitons, but it

>>doesnīt meet my Problem - in my opinion. Perhaps you have a tip for

>>me?

>>

>>Script:

>>

>>update

>>(

>>Select      a.x as wert

>>from         y a, z m

>>where      m.nr = x.nr

>>and          a.wert2 = '1'

>>and          a.wert3 = '10'

>>)

>>Set          wert = '6'

>>

>>The failure Code is: ORA-01779: cannot modify a column which maps to

>>an non key-preserved table.

>>

>>Thatīs my Problem, I can't do anything with the information "a non

>>key-preserved Table".

>>

>>Anybody an idea? Anything is helpfull, THX

>>

>>regards

>>

>>Carsten Bliessen

>>

>

>