Re: SQL Problem

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

>