SQL update Problem

[jadexy]

Hallo,

I have 2 tables:

SQL> desc flurstuecke_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
FLST_ID NOT NULL NUMBER(8)
FLST_NR_ZAEHLER NOT NULL NUMBER(4)
FLST_NR_NENNER NUMBER(4)
ZUSATZ VARCHAR2(2)
FLUR_NR NOT NULL NUMBER(2)
GEMARK_SCHLUESSEL REF OF GEMARK_SCHLUESSEL_T
FLAECHE SDO_GEOMETRY
GEMARK_SCHL NUMBER(4)

SQL> desc gemark_schluessel_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
GEMARK_ID NOT NULL NUMBER(8)
GEMARK_SCHL NUMBER(4)
GEMARK_NAME VARCHAR2(45)


In the columns gemark_schl are the same values and I want to connect the tables with this column. Now I wanna do and this works fine. but I don't want to do this for each gemark_schl in the table.

update flurstuecke_tab f set f.gemark_schluessel=(select ref(s) from gemark_schluessel_tab s where s.gemark_schl=101) where f.gemark_schl=101;

2027 rows updated.

So I tried this:

update flurstuecke_tab f set f.gemark_schluessel=(select ref(s) from gemark_schluessel_tab s INNER JOIN flurstuecke_tab f ON s.gemark_schl=f.gemark_schl); update flurstuecke_tab f set f.gemark_schluessel=(select ref(s) from gemark_schluessel_tab s INNER JOIN flurstuecke_tab f ON s.gemark_schl=f.gemark_schl)
* ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

update flurstuecke_tab f set f.gemark_schluessel=(select ref(s) from gemark_schluessel_tab s); update flurstuecke_tab f set f.gemark_schluessel=(select ref(s) from gemark_schluessel_tab s)
* ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

But it doesn't work. I don't know what to do else or whats wrong.

Thanks in advance
Tig

 

[lewisp]

You first update works fine because the sub-query only returns one row for each row in the main table that you want to update. For it to update 2027 rows there must be 2027 rows in FLURSTUECKE_TAB that have a GEMARK_SCHL = 101.

What do you mean when you say that you don't want to do this for each gemark_schl in the table?

 

[roedelfroe]

Hi jadexy,

it would be nice, if you post your questions in ONE Forum only.

Don't be impatient, a lot of Members are living and working in the US and they are several hours behind your time (guess you are located in Germany or Austria, aren't you?).

Regards

Roland