ORA-01427: single-row subquery returns more than one row / Was übersehe ich?

[Cigar]

Hallo,

da ich hier neu bin und dies mein erster Thread ist ... kurze Eigenberschreibung:
Geschlecht: m
Alter: 32
Job: Softwareentwickler, DB-Admin, IT-Manager ... kurz Afa (Ar... für alles)

So nu aber zu meinem Problem:

Folgender Zustand:
Zwei Tabellen (Zeichnung & Matind) sind über eine dritte (Ass_DesignObject_S4Document2) miteinander verbunden. Dies wird einfach über die Primärschlüssel der beiden ersten Tabellen realisiert. Also ein nicht normalisiertes Schema. (Ist nicht auf meinem Mist gewachsen)

Ich möchte nun:

1. Bei allen Zeichnungen welche mehrere Werte für s4_group_id auf der Matind-Seite haben die s4_group_id auf den Wert 8321 setzen.
2. Bei allen Zeichnungen die nur eine s4_group_id haben diese von Matind übernehmen

Dies wollte ich mit folgenden Statements ereichen:

update Zeichnung 
set s4_group_id = 8321
where 1 < (select count(DISTINCT MATIND.s4_group_id) 
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid);
commit;

update Zeichnung 
set s4_group_id = (select DISTINCT MATIND.s4_group_id 
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid) 
where s4_group_id <> 8321;
commit;

Leider erhalte ich beim zweiten update Befehl den ORA-01427.
Was zum Donner übersehe ich? HILFE!!

Im Voraus schonmal vielen Dank für eure Antworten.


Gruß

Cigar

[kuemmelchen]

set s4_group_id = (select DISTINCT MATIND.s4_group_id

Du möchtest der Spalte s4_group_id einen neuen Wert zuweisen (wegen =). Das setzt aber voraus, dass deine Unterabfrage genau einen Wert liefert. Sie liefert aber eine Menge Werten zurück. In einer Variablen der angegebenen Form darfst du aber gleichzeitig nur einen Wert hinterlegen.

Versuche deine Abfrage umzuschreiben.
Beispiel: QUOTE]set s4_group_id IN (select DISTINCT MATIND.s4_group_id
oder set s4_group_id ANY= (select DISTINCT MATIND.s4_group_id [/QUOTE]

kuemmelchen

[Cigar]

Hallo kuemmelchen,

danke für deine Antwort.

Allerdings, nach meiner Logik sollte dieser select nur einen Wert liefern. Denn mit dem ersten update-Befehl habe ich allen Datensätzen in der Tabelle Zeichnung die s4_group_id 8321 zugewiesen, bei denen dieser select mehr als einen Wert zurück gegeben hat. Im zweiten update-befehl habe ich dann explizit diese Zeichnungen vom update ausgeschlossen.
Oder habe ich einen Denkfehler?

Cigar

[dimitri]

Oder habe ich einen Denkfehler?

Führ das SQL doch einfach mal aus, dann siehst Du ja welche Sätze doppelt sind.
Ggf kannst Du ja noch mittles count(*), group by und having count(*)>1 sehen welche Einträge mehrfach auftreten.

Dim

[Cigar]

Hallo,

die Abfrage:

select s4_oid
from Zeichnung
where s4_group_id <> 8321
and (select count(DISTINCT MATIND.s4_group_id)
from matind MATIND, Ass_DesignObject_S4Document2
where first_reference = Zeichnung.s4_oid
and second_reference = matind.s4_oid) > 1;

liefert als Ergebnis 0 Zeilen.

Jetzt bin ich erstrecht ratlos...

[dimitri]

Die Abfrage sollte auch so lauten:

select count(distinct MATIND.s4_group_id),MATIND.s4_group_id
from matind MATIND, Ass_DesignObject_S4Document2,zeichnung 
where first_reference = Zeichnung.s4_oid         
and second_reference = matind.s4_oid
and zeichnung.s4_group_id <> 8321
group by MATIND.s4_group_id
having count(distinct MATIND.s4_group_id) > 1

Dim

[kuemmelchen]

Bei allen Zeichnungen welche mehrere Werte für s4_group_id auf der Matind-Seite haben die s4_group_id auf den Wert 8321 setzen.

soll durch folgende Abfrage realisiert werden:

1 <(select count(DISTINCT MATIND.s4_group_id)...

Denkfehler? Ja.
Angenommen, es sind folgende Werte für s4_group_id vorhanden:
1
2
3
3
2
...
Dein COUNT liefert für jede s4_group_id immer den Wert 1:
s4_group_id anzahl
1.....................1
2.....................1
3.....................1
Das DISTINCT innerhalb von count (DISTINCT s4_group_id) bewirkt, dass jede s4_group_id nur einmal übergeben wird, d.h. die Anzahl ist immer 1. Und 1 ist eben nicht größer als 1.
Für deinen Fall musst du das DISTINCT innerhalb von COUNT weglassen:

1 <(select count( MATIND.s4_group_id)...

kuemmelchen

[Cigar]

Hallo dimitri,
danke für deine Hilfe. Aber auch dein select gibt 0 Zeilen zurück.
Ergo, der update sollte klappen ... tut er aber nicht
Ich kappiers nicht.

Hallo kuemmelchen,

danke auch dir. Aber der Subselect sollte doch schon der gleiche wie aus dem Update sein. Sonst habe ich ja eine ganz andere Situation. Und die wäre nicht mit dem Update-Befehl vergleichbar.

Und sowas kurz vorm Wochenende

[kuemmelchen]

Führe doch mal folgende Tests durch
1. mit DISTINCT

select count(DISTINCT MATIND.s4_group_id), MATIND.s4_group_id
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid;

und 2. ohne DISTINCT

select count( MATIND.s4_group_id) , MATIND.s4_group_id
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid;

Welche Datensätze werden im ersten bzw. zweiten Fall ausgegeben?

kuemmelchen

[Cigar]

Mit DISTINCT:

COUNT(DISTINCTMATIND.S4_GROUP_ID) S4_GROUP_ID
1 0
1 4993
1 5249
1 5505
1 5761
1 6017
1 6273
1 6529
1 6785
1 7041
1 7297
0 NULL

Ohne DISTINCT:
COUNT(MATIND.S4_GROUP_ID) S4_GROUP_ID
99975 0
1215 4993
1454 5249
107 5505
25 5761
10 6017
1 6273
36 6529
176 6785
79 7041
36669 7297
0 NULL


Könnte eventuell der NULL-Wert das Problem sein? Wieso wird der überhaupt aufgeführt, wenn es doch eigentlich (steht ja 0 NULL) keinen Datensatz mit NULL gibt?

[kuemmelchen]

COUNT(DISTINCTMATIND.S4_GROUP_ID) S4_GROUP_ID
1 0
1 4993
1 5249
1 5505
1 5761
1 6017
1 6273
1 6529
1 6785
1 7041
1 7297
0 NULL

Das Ergebnis (mit DISTINCT) bedeutet doch, dass die Anzahl jedes mal 1 ist (außer die beiden Ausnahmen mit 0).
In deinem UPDATE

update Zeichnung 
set s4_group_id = 8321
where 1 < (select count(DISTINCT MATIND.s4_group_id) 
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid);

hast du als Bedingung festgelegt:
WHERE 1 < (SELECT count(DISTINCT MATIND.s4_group_id) ...).
Wenn du nun weist, dass die Anzahl nur den Wert 1 liefert, so ist die Bedingung niemals erfüllt ( 1 ist nicht kleiner als 1). Somit werden 0 Zeilen aktualisiert.

Setzt doch deine beiden UPDATE-Anweisungen mal ohne DISTINCT ab.

Ohne DISTINCT:
COUNT(MATIND.S4_GROUP_ID) S4_GROUP_ID
99975 0
1215 4993
1454 5249
107 5505
25 5761
10 6017
1 6273
36 6529
176 6785
79 7041
36669 7297
0 NULL

Dein UPDATE wird nun Aktualisierungen vornehmen, außer für die s4_group_id 6273 und NULL. In beiden Fällen ist die Anzahl nicht größer als 1.

kuemmelchen

[Cigar]

OK,
die nicht initialisierten Datensätze waren es auch nicht.
Die Zeilen für s4_group_id = NULL könnt ihr euch wegdenken. Ich habe die entsprechenden Datensätze initialisiert, leider hat das an der Fehlermeldung nichts geändert.

... to be continued ...<br>

[Cigar]

@kuemmelchen:

Ich glaube ich habe schlecht beschrieben wo mein Problem liegt.

Das erste Update, welches du in deinem Post stehen hast,
ist schon längst ohne Probleme gelaufen. Ich habe es nur aufgeführt weil es (nach meiner Logik *zweifel*) die Voraussetzung für das zweite Update ist.
Probleme bereitet aber nur das zweite update.

Ich hatte mir das eigentlich so gedacht:

1. Update setzt alle Datensätze der Tabelle Zeichnung die im zweiten Update Probleme bereiten würden auf eine definierte s4_group_id (8321).
2. Update aktualisiert alle Zeichnungen bis auf die mit s4_group_id 8321 und übernimmt für die übrigen die Werte aus der Matind Tabelle.(Die "übrigen" sollten nach dem ersten Update alle nur eine s4_group_id haben.)



Cigar

[kuemmelchen]

update Zeichnung set s4_group_id = 8321

Hier wird s4_group_id genau ein Wert (8321) zugewiesen.

update Zeichnung set s4_group_id = (select DISTINCT MATIND.s4_group_id

Das selbe muss auch hier gelten; s4_group_is = genau einen Wert.
Deine Unterabfrage liefert aber mehr als einen Wert zurück (daher auch der genannte Fehler).
Teste die Unterabfrage mal separat:

select DISTINCT MATIND.s4_group_id 
from matind MATIND, Ass_DesignObject_S4Document2 
where first_reference = Zeichnung.s4_oid 
and second_reference = matind.s4_oid) 
where s4_group_id <> 8321

Du wirst hier sicherlich mehr als einen Wert zurückgeliefert bekommen.

kuemmelchen

[Cigar]

Hallo Kuemmelchen,

die Abfrage:

select s4_oid 
 from Zeichnung 
 where s4_group_id <> 8321
 and (select count(DISTINCT MATIND.s4_group_id) 
           from matind MATIND, Ass_DesignObject_S4Document2 
           where first_reference = Zeichnung.s4_oid 
           and second_reference = matind.s4_oid) > 1;

testet ja genau die Unterabfrage. Denn wenn die Unterabfrage für irgendeine Zeichnung mehr als einen Wert liefern würde müsste man das ja sehen. Es werden aber keine Zeilen zurückgegeben.
--> Ergo es wird kein oder genau 1 Wert zurückgegeben und zwar für alle Zeichnungen bei denen s4_group_id <> 8321 gilt.


Cigar