Nummerierung mit nur einem UPDATE und mit korrelierter Subquery

Hallo Forum Mitglieder,

ich verzweifle gerade an einer Aufgebe für mein Studium. Diese Lautet wie folgt:

Nummerieren Sie alle Autoren in der BuchAutoren-Tabelle durch (Geisler = 1, Goethe = 2, usw.). Fügen Sie diese Nummern in die Spalte “Ordnung” ein mithilfe einer einzigen UPDATE-Anweisung. Lösen Sie die Aufgabe mit korrelierter Unterabfrage (Correlated Subquery).

Die Tabelle sieht so aus:




folgende Datentypen:
titel=non_empty_string
name=non_empty_string
nr=integer
ordnung=integer

Mein Problem ist die aufsteigende Nummerierung.

Ich will ja nicht deine Hausaufgaben machen … aber vielleicht hilft das ja weiter:

Correlated Subqueries - Google-Suche

Ich wusst ja noch garnicht, dass subqueries die zur übergeordneten query referenzieren nen eigenen namen haben … da solltest du alle Informationen finden.

Du darfst auch mal nach “update with correlated suqueries” suchen … da findest wahrscheinlich fast die Lösung deines Problems.

Danke für deine Hinweise, jedoch würde ich mich nicht an dieses Forum wenden, wenn ich diese Varianten(Googeln) nicht schon hinter mir hätte.

Die von Dir genannten Datentypen lassen mich zweifeln, ob das auch wirklich mit PostgreSQL gemacht werden soll.

Ich hätte jetzt spontan eine Idee, mit einem Update-Befehl, allerdings unter Verwendung einer Sequence. ORACLE kennt z.B. einen Rowcount, da wäre dies möglicherweise wirklich in einem Rutsch machbar.

Andreas



test=# select * from o;
 name | o
------+---
 foo  |
 foo  |
 bar  |
 blub |
 blub |
 blub |
(6 Zeilen)

Zeit: 0,147 ms
test=*# create sequence my_seq;
CREATE SEQUENCE
Zeit: 1,379 ms
test=*# update o set o = foo.s from (select t.name, nextval('my_seq') as s from (select distinct name from o order by name )t)foo where o.name=foo.name;
UPDATE 6
Zeit: 0,582 ms
test=*# select * from o;
 name | o
------+---
 foo  | 3
 foo  | 3
 bar  | 1
 blub | 2
 blub | 2
 blub | 2
(6 Zeilen)

Andreas

Also was ich bereits versuche ist folgendes :

UPDATE buchautoren x SET ordnung = (SELECT MAX(ordnung) FROM buchautoren) + 1 WHERE x.name IN (SELECT distinct(name) FROM buchautoren)

Das Problem ist : wenn man zu einem NULL eine 1 addiert kommt NULL raus. Weiterhin würde diese ABFRAGE nur alle werte erhöhen. Was man hier jedoch am liebsten hätte wäre eine Schleife… naja unser Prof geht schon gut ab :astonished: .

Das ist kein Problem, das ist SQL-Standard und erwünschtes Verhalten.

Wenn du kein NULL dort haben möchtest, definiere die Spalte mit NOT NULL und setze ggf. einen Default Wert wie z.B. 0 ein.

Ersetze MAX(ordnung) durch MAX(CASE WHEN ordnung IS NULL THEN 0 ELSE ordnung)
oder alternativ MAX(CASE WHEN ordnung IS NOT NULL THEN ordnung ELSE 0) - das sollte dein NULL Problem lösen …

Also
UPDATE buchautoren x
SET ordnung = (SELECT MAX(CASE WHEN ordnung IS NULL THEN 0 ELSE ordnung
) FROM buchautoren) + 1
WHERE x.name IN (SELECT distinct(name) FROM
buchautoren)

Das ist halt Schwachsinns-SQL für Professoren … aber da musst du durch :slight_smile:

Weiterhin:
gruppierst du nicht richtig und correlierst (bla) falsch … das mit der Corellation ist so gemeint, dass sich die Subquery eben immer nur auf eine Zeile der Übergeordneten Query bezieht … hier eben das UPDATE:

du willst ja über den namen gruppieren … und dann jede ROW der Tabelle in UPDATE die GLEICH der GRUPPE der Subquery ist (entspricht) - dafuer gibt es das gute alte GROUP BY … Du willst ja das MAX der Ordnung von genau einem namen, dem der “aktuellen” ROW in deinem UPDATE …

Also in etwa so:

UPDATE buchautoren x
SET ordnung = (SELECT MAX(… y.ordnung … ) FROM buchautoren) + 1
WHERE x.name = (SELECT y.name
FROM buchautoren y
GROUP BY y.name)

natuerlich das MAX(…) um das CASE WHEN erweitern … siehe oben …

Deine Query updated immer alles, da dein DISTINCT dir eine Datenmenge über ALLE namen liefert. Und das Update somit natuerlich im WHERE … IN immer ALLE namen betrifft. In deinem Beispiel gibt es eben keine Wirkliche Beziehung, bzw Korrelation – Wikipedia zwischen einer ROW im UPDATE und der Datenmenge deines Subselects …

In meinem Beispiel siehst du statt des IN ein x.name = y.name … somit Betrifft das Subselect immer genau denjenigen namen (dank GROUP BY und aggregated MAX) - der aktuellen ROW geupdated wird → Korrelation

UPDATE buchautoren x
SET ordnung = (SELECT MAX(CASE WHEN ordnung IS NOT NULL THEN ordnung ELSE 0) FROM buchautoren) + 1
WHERE x.name = (SELECT y.name
FROM buchautoren y
GROUP BY y.name)



Wenn ich diese Abfrage starte kriege ich ein Fehler bei Zeichen 96 “)”, das ist wo +1 steht. komische Sache…

schau dir CASE WHEN … THEN … ELSE … END an …

selbst dieser Unsinn hier funktioniert:

UPDATE buchautoren x
SET ordnung =
(SELECT (CASE WHEN MAX(ordnung) IS NULL THEN 0 ELSE MAX(ordnung) END) + 1 FROM buchautoren
WHERE x.name = (SELECT y.name
FROM buchautoren y
GROUP BY y.name))

Ein bisschen mehr Ehrgeiz bitte :slight_smile:

Dabei kommt dann allerdings ein: ERROR: more than one row returned by a subquery used as an expression :confused:

Nummerieren Sie alle Autoren in der BuchAutoren-Tabelle durch (Geisler = 1, Goethe = 2, usw.). Fügen Sie diese Nummern in die Spalte “Ordnung” ein mithilfe einer einzigen UPDATE-Anweisung. Lösen Sie die Aufgabe mit korrelierter Unterabfrage (Correlated Subquery).

Das macht die ganze sache natuerlich viel einfacher, da wir garnichts mit MAX brauchen. Das mit MAX konnte auch so nicht funktionieren, da MAX nicht fuer jede ROW neu berechnet wird … Schade eigentlich …

Nun denn … deine Aufgabe löst man ganz einfach:

[color=Blue]UPDATE [/color]buchautoren x
SET [color=Blue]ordnung =[/color]
([U][color=Blue]SELECT y.nr[/color] FROM buchautoren y
 WHERE [color=Red]y.titel [/color]= x.titel and [color=Red]y.name[/color] = x.name[/U])

Also wir sollen ja nix weiter tun, als in EINEM Update die Nr in Ordnung “kopieren” / zuweisen … da brauchen wir auch kein MAX und so kram …
Das alles sollen wir mit einer korrelierenden Subselect/Subquery machen … die also - wie der Name sagt - auf das Update “referenziert” … uns also einen Wert liefert (Nr) die wir als Ordnung in eine Row stopfen koennen.

Dazu muessen wir die Ordnung mittels dieser k. Subquery selektieren … fuer jede Row - also brauchen wir einen sog. Primary-Key (Wenn du das vergessen hast aus den Vorlesungen → Hausaufgaben von mir an dich) … der besteht in dem Beispiel sehr wahrscheinlich aus TITEL und NAME …

Wären diese Felder kein Primary-Key - dann wuerdest du genau den Fehler bekommen den du zuletzt geschrieben hattest, da das Subselect ja dann mehr als nur einen Datensatz liefern wuerde. Und das ist bei dem = Operator im Set nicht erlaubt, weil du einem Feld ja nur einen Wert zuweisen darfst.