Lookup-Tabelle updaten, vorm Löschen von Redundanzen

[Tobad]

Hallo,

ich bin ein Neuling in der Datenbankwelt und ich habe ein Problem, das ich allein nicht lösen kann. Ich hoffe, dass es hier hereinpasst, ich denke nicht, dass es ein spezifisches Problem von MySQL ist, das ich nutze.

Vielleicht versuche ich das Problem noch einmal neu zu formulieren:

Ich habe drei Tabellen A, B, C. In A sind Informationen zu Adressen, in B zu Personen. C ist eine Tabelle, die diese Datensätze zuordnet, da mehrere Personen eine Adresse teilen können und mehrere Adressen zu einer Person gehören können.

A ist aus Importgründen genauso groß wie C, das heißt in A sind Adressen doppelt oder mehrfach angeführt. Wie kann ich die Spalte ID_A in C so updaten, dass in den entsprechenden Datensätzen in C immer nur die kleinste ID_A erscheint und ich in A dann die anderen löschen kann?

Ich hoffe ich habe alle nötigen Informationen angegeben.

Danke im Voraus!

[Tobad]

Hallo,

ist das Problem zu einfach, oder falsch gepostet?

Grüße Tobias Adolph

[Falk Prüfer]

Hallo,

...Ich hoffe, dass es hier hereinpasst, ich denke nicht, dass es ein spezifisches Problem von MySQL ist, das ich nutze.

Es ist aber auch kein triviales SQL-Problem . Und da MySQL gewissen Beschränckungen unterliegt was zum Bsp. Subquerys (geht erst ab 4.1) angeht, ist es schon irgendwo MySQL spezifisch.

Wenn dir die verwendete A-ID egal ist müßte das Update so gehen:
[highlight=sql]
update table_c set
ID_A = (select min(id)
from table_a a
where (a.plz, a.stadt) = (select a1.plz, a1.stadt
from table_a a1
where a1.id = ID_A))
[/highlight]

Danach kannst du dann die Einträge in A löschen, die keine ID_A mehr in C haben.

Gruß Falk

P.S.: Ich habs nicht getestet, also bitte vorher ein Backup machen!

[frauwue]

Hi,

vom Prinzip ist das wohl richtig, was Falk schreibt; als Eindeutigkeitskriterium für eine Adresse sollte man aber schon die Straße und Hausnummer dazunehmen. Problematisch wird es nur, wenn die Schreibweise der Straßennamen bei gleicher Adresse variiert (Str., Straße u. dergleichen).

Gruß

[Falk Prüfer]

...als Eindeutigkeitskriterium für eine Adresse sollte man aber schon die Straße und Hausnummer dazunehmen.

OK, ich hätte dazuschreiben sollen, das ich hier nur PLZ und Stadt beispielhaft verwende. Das Thema Adressen ist wesentlich komplexer und da uns Tobad sein Tabellenschema nicht verraten hat ...

Gruß Falk

[Tobad]

Hallo und danke für die Antwort. War mir nicht sicher, ob das Problem wirklich komplex ist oder nicht.

Ich habe versucht die Idee von Falk in eine MySQL-kompatible Lösung zu bringen:

UPDATE 	table_c
SET	table_c.id_a = (SELECT 	MIN(id_a) 
FROM table_a, 
(SELECT strasse, plz, ort FROM table_a WHERE table_a.id_a = table_c.id_a) AS a1
WHERE a1.strasse = table_a.strasse
AND a1.plz = table_a.plz
AND a1.ort = table_a.ort)

Das ganze mag MySQL nicht, es sagt, dass ich einen Syntax-Fehler habe. Ich hoffe, dass ich mich nirgendwo vertippt habe und das ich Falk so richtig verstanden habe, aber ich gehe davon aus, dass die Unterabfrage in der UPDATE-Anweisung nicht funktioniert. Kann man das irgendwie austricksen?

Spuckt die SELECT-Anweisung eigentlich dann nur einen Wert (den mit der kleinsten id von table_a, zu der es einen redundanten Eintrag gibt) aus? Dann würde in table_c ja alles auf einen Eintrag in a referenziert werden. Dann müsste man an die UPDATE-Anweisung ja noch irgendwie eine WHERE-Bedingung knüpfen?

Danke auf jeden Fall für eure Mühe.

Grüße,
Tobias

P.S.: Wie kann ich denn die SQL-Syntax higlighten? Habe in den FAQ nur die Tags für HTML und PHP gefunden...

[Jürgen Thomas]

Ich glaube, eine schließende Klammer fehlt.

[highlight=sql]...[/highlight]
Außerdem solltest du immer sinnvoll einrücken, damit Sachen, die zusammengehören, auch als solche erkennbar sind - hier die verschiedenen SELECTs.

Gruß Jürgen

[Falk Prüfer]

Hallo,

...Ich habe versucht die Idee von Falk in eine MySQL-kompatible Lösung zu bringen:

meine Lösung sollte eigentlich unter MySQL funktionieren, es sei denn du verwendest eine Version vor 4.1.

...

UPDATE 	table_c
SET	table_c.id_a = (SELECT 	MIN(id_a) 
FROM table_a, 
(SELECT strasse, plz, ort FROM table_a WHERE table_a.id_a = table_c.id_a) AS a1
WHERE a1.strasse = table_a.strasse
AND a1.plz = table_a.plz
AND a1.ort = table_a.ort)

Das ganze mag MySQL nicht, es sagt, dass ich einen Syntax-Fehler habe.

Hier wäre es natürlich in erster Linie hilfreich die genau Fehlermeldung zu haben. Ich weiß das MySQL nicht viel mehr sagt als das es einen Syntaxfehler gibt, entscheidend hilfreich ist jedoch das was hinter "In der Nähe von..." steht.
Ich bin mir aber ziemlich sicher das er deinen Inline-Select (SELECT strasse, plz, ort FROM table_a WHERE table_a.id_a = table_c.id_a) AS a1 meint. In einem solchen Konstrukt kannst du dich nicht auf eine Zeile aus einer anderen Tabelle beziehen, da dieses Unterergebnis ermittelt wird BEVOR alle anderen Bedingungen (JOIN, WHERE) abgearbeitet werden.

...Spuckt die SELECT-Anweisung eigentlich dann nur einen Wert (den mit der kleinsten id von table_a, zu der es einen redundanten Eintrag gibt) aus? Dann würde in table_c ja alles auf einen Eintrag in a referenziert werden. Dann müsste man an die UPDATE-Anweisung ja noch irgendwie eine WHERE-Bedingung knüpfen?

Nein, das SET ermittelt ja die kleinste ID zu den gerade per ID_A referenzierten Adressangaben. Die Where-Bedingung steht hier quasi im Subselect. Eine Where-Bedingung für die Update-Anweisung bräuchtest du nur, wenn nur bestimmte Einträge aus table_c geändert werden sollen.

Gruß Falk

[Falk Prüfer]

Ergänzung:
Mit JOIN statt doppeltem Subselect müsste es so aussehen:
[highlight=sql]
UPDATE table_c c SET
ID_A = (SELECT min(a.id)
FROM table_a a,
inner join table_a a1 on a1.plz = a.plz
and a1.stadt = a.stadt
and ...
where a1.id = c.ID_A
[/highlight]

Gruß Falk

[Tobad]

Hallo Falk,

vielen Dank für deine Mühen. Ich benutze Version 5.1.37. Ich verstehe nur nicht, was beispielsweise "table_c c" bei der UPDATE-Anweisung bedeutet, also das Leerzeichen...

Die Fehlermeldung (mit adr für A und adrl für C):

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table adrl SET adrl.id_adr = (SELECT MIN(id_adr) FROM adr, (SELECT ad' at line 1"

Da bezieht er sich doch nicht auf das Inline-SELECT? Hier die Original-Eingabe
[highlight=sql]UPDATE table adrl
SET adrl.id_adr = (SELECT MIN(id_adr)
FROM adr,
(SELECT adr_strasse, adr_plz, adr_ort
FROM adr
WHERE adr.id_adr = adrl.id_adr) AS dupl
WHERE dupl.strasse = adr.strasse
AND dupl.plz = adr.plz
AND dupl.ort = adr.ort)[/highlight]

Habe versucht das irgendwie einzurücken, aber die TAB-Taste funktioniert nicht und diese INDENT-Anweisung auch nicht...

Zitat von Tobad Beitrag anzeigen
...Spuckt die SELECT-Anweisung eigentlich dann nur einen Wert (den mit der kleinsten id von table_a, zu der es einen redundanten Eintrag gibt) aus? Dann würde in table_c ja alles auf einen Eintrag in a referenziert werden. Dann müsste man an die UPDATE-Anweisung ja noch irgendwie eine WHERE-Bedingung knüpfen?
Nein, das SET ermittelt ja die kleinste ID zu den gerade per ID_A referenzierten Adressangaben. Die Where-Bedingung steht hier quasi im Subselect. Eine Where-Bedingung für die Update-Anweisung bräuchtest du nur, wenn nur bestimmte Einträge aus table_c geändert werden sollen.

Achso, wir ändern alle Einträge in table_c/adrl, nur wird ein Teil eben identisch bleiben, weil es schon min(id_a) hat?

Grüße,
Tobias

[Jürgen Thomas]

Das ist falsch:
[highlight=sql]UPDATE TABLE adrl[/highlight]
Das ist richtig:
[highlight=sql]UPDATE adrl[/highlight]
Bitte gleich alle relevanten Informationen liefern, das erleichtert die Fehlersuche und die Hilfe ungemein! Du siehst, dass die Fehlermeldung ganz genau war: "in der Nähe von table" und "in Zeile 1".

Jürgen

PS. Einrückungen müssen i.d.R. mit Leerzeichen erfolgen.

[Falk Prüfer]

...Ich verstehe nur nicht, was beispielsweise "table_c c" bei der UPDATE-Anweisung bedeutet, also das Leerzeichen...

damit bekommt die zu ändernde Tabelle "table_c" den Alias "c". Man könnte auch
[highlight=sql]
...
table_c as c
...
[/highlight]
schreiben. Das 'AS' ist jedoch optional.

Mit dem Alias kann ich mich dann in der letzten Zeile des Subselect
[highlight=sql]
...
INNER JOIN table_a a1 ON a1.plz = a.plz
AND a1.stadt = a.stadt
AND ...
WHERE a1.id = c.ID_A
[/highlight]
auf die zu ändernde Tabelle beziehen. Dies ist dann wichtig, wenn der Feldname ID_A nicht nur in table_c vorkommt.

Gruß Falk

[Tobad]

Hallo zusammen,

leider tritt bei der folgenden Abfrage genau das ein, was ich befürchtet habe: Es wird bei allen Einträgen in adrl die kleinste Adress-Id aus adr eingetragen, die mehr als einmal in adr vorkommt. Nicht aber, wie benötigt, die kleinste adr_id, mit der identischen Adresse zu dem jeweiligen Datensatz aus der Lookup-Tabelle adrl

[highlight=sql]
UPDATE adrl
SET adrl.id_adr = (SELECT min(adr.id_adr)
FROM adr
WHERE (adr.strasse, adr.plz, adr.ort) = (SELECT a1.strasse, a1.plz, a1.ort
FROM adr a1
WHERE a1.id_adr = adr.id_adr))
[/highlight]

Was also m.E. fehlt ist eine Schleifenmöglichkeit, so dass die UPDATE-Anweisung immer einen Block an identischen Adressen arbeitet.

[Tobad]

Die Lösung

Ich habe eine Lösung für die Aufgabenstellung gefunden, nach tagelangem Grübeln und Dank eurer Hilfe.

Diese UPDATE-Anweisung ergibt das gewünschte Resultat:

[highlight=sql]
UPDATE adrl, (SELECT doppl.minimum, doppl.id_adr
FROM (SELECT adr.id_adr, min(dupl.id_adr) AS minimum
FROM adr, (SELECT adr.id_adr, strasse, plz, ort FROM adr) AS dupl
WHERE adr.strasse = dupl.strasse
AND adr.plz = dupl.plz
AND adr.ort = dupl.ort
GROUP BY adr.id_adr) AS doppl) AS trippl
SET adrl.id_adr = trippl.minimum
WHERE trippl.id_adr = adrl.id_adr
[/highlight]

Diese DELETE-Anweisung löscht die redundanten Datensätze:
[highlight=sql]
DELETE FROM adr
WHERE NOT EXISTS (SELECT *
FROM adrl
WHERE adrl.id_adr = adr.id_adr)
[/highlight]
Juhu! Jetzt kanns weiter gehen...