SQL update problem

[jadexy]

Hallo,

I have already posted and didn't come further. So I tried another way and added and attribute gemark_schl to my table flurstuecke_tab. Now I have this 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

 

[roedelfroe]

Hi,

your first update was working fine, because your subselect returns exactly one result for each row.

Your other update tries don't work, because your subselect is returning more the one result with Oracle just saying "Hey, and which of this result is the one you want???".

So, it would be helpful if you tell us, what exactly you want to do. What's your base and where do you want to go?

Regards

Roland

 

[jadexy]

Hallo,

originally that are my types and tables.

-- Gemarkschluessel
CREATE TYPE gemark_schluessel_t AS OBJECT(
gemark_id NUMBER(8),
gemark_schl NUMBER(4),
gemark_name VARCHAR2(45)
);
/

CREATE TABLE gemark_schluessel_tab OF gemark_schluessel_t(
constraint pk_gemark PRIMARY KEY(gemark_id)
);

-- Flurstueck
CREATE TYPE flurstueck_t AS OBJECT(
flst_id NUMBER(8),
flst_nr_zaehler NUMBER(4),
flst_nr_nenner NUMBER(4),
zusatz VARCHAR2(2),
flur_nr NUMBER(2),
gemark_schluessel REF gemark_schluessel_t,
flaeche SDO_GEOMETRY
);
/

CREATE TABLE flurstuecke_tab OF flurstueck_t(
constraint pk_flst PRIMARY KEY(flst_id),
constraint uq_flst UNIQUE(flst_nr_zaehler,flst_nr_nenner,zusatz,flur_nr),
flst_nr_zaehler NOT NULL,
flur_nr NOT NULL,
gemark_schluessel REFERENCES gemark_schluessel_tab
);

I have loaded data in my gemark_schluessel_tab with sql*loader and that worked fine. The file for my load to gemark_schluessel_tab looked like this (the id is filled with a trigger):

LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE GEMARK_SCHLUESSEL_TAB
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
gemark_schl,
gemark_name
)
BEGINDATA
101|Borna|

So that was fine as I already told.

Now I wanted to load data to flurstuecke_tab but I don't really know how I can load the data to gemark_schluessel in flurstuecke. The data for my flurstuecke_tab has data for the attributes flst_nr_zaehler,flst_nr_nenner,zusatz,
flur_nr and flaeche. And for the attribute gemark_schluessel there I have only a number in the data which meets the gemark_schl from gemark_schluessel_tab. The load-file looks like this (the 122 at the end of the data record is the gemark_schl that meets with one entry in from gemark_schluessel_tab):

LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE FLURSTUECKE_TAB
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
flst_nr_zaehler,
flst_nr_nenner,
zusatz,
flur_nr,
flaeche COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL,
SDO_SRID INTEGER EXTERNAL,
SDO_ELEM_INFO VARRAY TERMINATED BY '|/'
(X FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '|/'
(X FLOAT EXTERNAL)
)
--- DON'T KNOW WHETHER THIS IS RIGHT
gemark_schluessel REF(CONSTANT 'GEMARK_SCHLUESSEL_TAB',gemark_schl),
--- DON'T KNOW WHAT TO WRITE HERE
)
BEGINDATA
216|||1|2003|82032|1|1003|1|/4557856.144|5632083.278|4557843.877|5632050.083|4557841.635|5632043.592|4557826.557|5632006.317|4557823.831|5631998.834|4557735.263|5632065.581|4557734.860|5632065.885|4557759.729|5632138.030|4557862.595|5632101.970|4557856.144|5632083.278|/122|

Can I use with this structure the load of REF column and how. I really don't know.

Thanks Tig

 

[jadexy]

Because it doesn't worked I have added a new attribute in flurstuecke_t gemark_schl and I wanted to update after the load (which worked fine) but the statement didn't work.

 

[juliane26]

Tig,

to what you wrote in your first statement:

First of all check, whether you get more then one entry for each GEMARK_SCHL in GEMARK_SCHLUESSEL_TAB.

select count(*), gemark_schl from gemark_schluessel_tab group by gemark_schl

for each value it should not return more than 1. Otherwise you just cant match 1 to 1 and your subselect fails because of these values.

If possible clean it, try it again and it should work fine.




Juliane

 

[jadexy]

Hallo,

thanks for your advice. Now I change my query to this

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

and it works fine.

Thanks Tig