Mit PHPMyAdmin (MySQL) doppelte Einträge löschen?

[Max Power]

Ich möchte aus meiner Datenbanktabelle "newsletter" alle Einträge löschen, bei der der Wert der Spalte "email" doppelt vorkommt.
Ist das mit MySQL überhaupt zu realisieren?

Also so geht's schon mal nicht?
DELETE FROM newsletter
GROUP BY email
HAVING COUNT(*) > '1';

???,
Max

[Matthias Jütte]

Hallo Max!

Ich kann dir leider nicht sagen, ob das mit MySQL geht oder nicht, aber in der Zeit, wo hier und bei dir die Köpfe rauchen ist doch auch schnelle ein PHP-Skript geschrieben:

$query = "SELECT id, email FROM table";
$res = mysql_query($query);

while (list($id, $email) = mysql_fetch_array($res)) {
    $query2 = "DELETE FROM table WHERE email='$email' and id != $id";
    mysql_query($query2);
}

Das setzt voraus, daß das erste Vorkommen einer E-Mail-Adresse auch immer der Datensatz ist, der behalten werden soll.

Hoffe ich hab's richtig verstanden und den Code hier richtig aus dem Kopf hingeklatscht. Prinzip sollte aber selbst dann klar sein, wenn nicht.

Gruß

Matthias

P.S.: Ich erhebe nicht den Anspruch, daß dies eine effizienter "Algorithmus" ist. Hier zählt lediglich das Ergebnis.-

[dedlfix]

echo $begrüßung;

Ich kann dir leider nicht sagen, ob das mit MySQL geht oder nicht, aber in der Zeit, wo hier und bei dir die Köpfe rauchen ist doch auch schnelle ein PHP-Skript geschrieben:

Nein, mit deinem Script geht das nicht.

ID email
1 email1
2 email1
...

Beim ersten Schleifendurchlauf ist id = 1 und email = email1. Damit wird in inneren Query der Datensatz mit der ID 2 gelöscht (email1 = email1 and 1 != 2).
Der zweite Durchlauf geht mit id = 2 und email = email1 ins Rennen und löscht den Datensatz mit der ID 1 (email1 = email1 and 2 != 1). Ups...

Hattest du angenommen, dass durch den Löschbefehl die bereits ermittelte Ergebnismenge der anderen Query beeinflusst wird?

Hoffe ich hab's richtig verstanden und den Code hier richtig aus dem Kopf hingeklatscht.

Da brauchen wir wohl doch etwas Rauch... :-)

Wenn dann gehts eher so (MySQL 4.1 wegen der Subselects vorausgesetzt):

SELECT * FROM test WHERE id IN (
  SELECT MAX(id)
  FROM test
  GROUP BY email
  HAVING COUNT(*) > 1
)

Für Version 3.28 sollte es mit einer temporären Tabelle gehen. Temp. Tabellen leben nur solange die Verbindung zur DB besteht. Beide Befehle müssen deshalb zusammen in das phpMyAdmin-SQL-Eingabefeld eingegeben werden.

CREATE TEMPORARY TABLE temptable
  SELECT max(id) x
  FROM test
  GROUP BY email
  HAVING count(*) > 1;
SELECT * FROM test, temptable WHERE id=x;

Beide Varianten berücksichtigen für Mehrfacheinträge immer nur einen Eintrag. Wenn man sie so oft laufen lässt, bis keine betroffenen Spalten mehr gemeldet werden, haben sie ihren Zweck erfüllt.

Zum überprüfen, dass die richtigen Datensätze ermittelt werden, lasse ich die nur ausgeben. Daraus eine Löschabfrage zu erstellen sollt enicht das Problem sein.

Hier zählt lediglich das Ergebnis.

In diesem Forum zählt eher der Lerneffekt auf dem Weg dahin :-)

echo "$verabschiedung $name";

[Ilja]

yo,

Da brauchen wir wohl doch etwas Rauch... :-)

ich hoffe, ich kann da als nichtraucher auch einen beitrag liefern... ;-)

bei beiden möglichkeiten mit unterabfrage und ohne ist dir meiner meinung nach ein kleiner logischer fehler unterlaufen. sprich du versuchst beim vorkommen mehrerer gleicher e-mails immer die max(id) mit ins boot zu nehmen, was sicherlich so erst einmal auch ein guter ansatz ist.

allerdings könnte das HAVING COUNT(*) > 1  in beiden versionen probleme bereiten. schließlich sollte das ziel sein, eine liste der id's zu bekommen, die erhalten werden sollen oder aber eine liste der Id's, die gelöscht werden sollen. und die Max(id) geht den weg alle id's zu bekommen, die letztlich erhalten werden sollen. schließlich können ja auch drei oder mehr datensätze die gleiche e-mail besitzen. da würde ich dann aber auch die id's mir reinnehmen, die nur ein datensatz für eine e-mail adresse haben, sprich das Having komplett entfernen. diese sollen ja auch erhalten bleiben....

Ilja

[dedlfix]

echo $begrüßung;

Da brauchen wir wohl doch etwas Rauch... :-)

ich hoffe, ich kann da als nichtraucher auch einen beitrag liefern... ;-)

Der Papst ist auch Nichtraucher... :-)

bei beiden möglichkeiten mit unterabfrage und ohne ist dir meiner meinung nach ein kleiner logischer fehler unterlaufen. sprich du versuchst beim vorkommen mehrerer gleicher e-mails immer die max(id) mit ins boot zu nehmen, was sicherlich so erst einmal auch ein guter ansatz ist.

Erstmal wird für jede eindeutige Email durch die Gruppierung und das max(id) eine ID ermittelt. Durch das HAVING COUNT(*) > 1 fallen dann die Datensätze raus, die auf jeden Fall erhalten bleiben sollen. Von den zu löschenden bleibt immer nur einer übrig, auch wenn es mehr als zwei gleiche Emails gab. Deswegen muss man das ggf. mehrfach anstoßen. Das habe ich in Kauf genommen, da sowieso eine händische Lösung (für phpMyAdmin) gesucht war.

allerdings könnte das HAVING COUNT(*) > 1  in beiden versionen probleme bereiten.

Das HAVING wirkt zusammen mit der über die Gruppierung gezählten Elemente. Ist es das, was dir Bauchschmerzen bereitet?
Mir ist auch nicht ganz klar, wie das COUNT(*) noch an sein richtiges Ergebnis kommt. Das HAVING läuft ja erst, wenn schon fast alles vorbei ist. Sprich: In der Ergebnismenge vor dem HAVING müsste sich nur ein Datensatz mit der max. ID pro Gruppe befinden. Keine Ahnung, wie das MySQL dann noch zählt.

schließlich sollte das ziel sein, eine liste der id's zu bekommen, die erhalten werden sollen oder aber eine liste der Id's, die gelöscht werden sollen.

Letzeres hatte ich bei meinem Lösungsansatz im Sinn. Lass dich mal nicht von dem (äußeren) SELECT verwirren. Das nahm ich nur, um die Datensätze anzuzeigen, die gelöscht werden können.

Im Gegensatz zu Matthias habe ich beide Varianten (unter MySQL 4.1) getestet und das Ergebnis entsprach meinen Erwartungen: Von mehrfachen Einträgen wurde jeweils einer angezeigt.

Ich probierte vorhin allerdings kein DELETE. Dies wollte ich grade nachholen, scheiterte aber in der 4.1-Variante an:

"One restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, and UPDATE."

Und auch in der Temp-Table-Variante hatte ich kein Glück :-(
Das DELETE müsste so aussehen:

DELETE test FROM test, temptable WHERE id = x;

Dies wird unter MySQL 4.1 wie gewünscht abgearbeitet. Diese Syntax (multi-table delete format) wird jedoch erst ab MySQL 4.0 unterstützt.

Also wird für 3.xx doch ein Script herhalten müssen.

echo "$verabschiedung $name";

[Ilja]

yo,

Durch das HAVING COUNT(*) > 1 fallen dann die Datensätze raus, die auf jeden Fall erhalten bleiben sollen. Von den zu löschenden bleibt immer nur einer übrig, auch wenn es mehr als zwei gleiche Emails gab. Deswegen muss man das ggf. mehrfach anstoßen. Das habe ich in Kauf genommen, da sowieso eine händische Lösung (für phpMyAdmin) gesucht war.

genau da liegt das problem, lass das HAVING weg und du hast automatisch eine abfrage aller Id's, die nicht gelöscht werden sollen. dann brauchst du auch nicht mehere dürschläufe, sondern nur einen, um alle anderen datensätze zu löschen, sprich den operator NOT IN verwenden. du bist ein wenig durcheinander gekommen, welche datensätze erhalten bleiben sollen und welche gelöscht werden sollen.

Das HAVING wirkt zusammen mit der über die Gruppierung gezählten Elemente. Ist es das, was dir Bauchschmerzen bereitet?

nicht wirklich, ich kenne die auswirkungen von HAVING. da wird mir eher mein bier morgen früh kopfschmerzen bereiten. ;-)

Ilja

[dedlfix]

echo $begrüßung;

du bist ein wenig durcheinander gekommen, welche datensätze erhalten bleiben sollen und welche gelöscht werden sollen.

Sieht das so aus? Für mich war immer klar, dass ich in der inneren Abfrage / in der temp. Tabelle die zu löschenden Datensätze haben wollte bzw. immer einen Teil davon.
Dein NOT IN dreht lediglich[1] den ganzen Spieß um und löst die Aufgabenstellung eleganter.

echo "$verabschiedung $name";

[1] sag ich jetzt mal so in meinem jugendlichen Leichtsinn...

[Ilja]

yo,

Sieht das so aus? Für mich war immer klar, dass ich in der inneren Abfrage / in der temp. Tabelle die zu löschenden Datensätze haben wollte bzw. immer einen Teil davon.

nun, sicherlich kann man mit MAX(id) immer die id's heausfiltern, die gelöcht werden sollen. aber das ist wirklich sehr unelegant. schließlich weiß man nicht, wieviele durchläufe man damit machen muss. warum kompliziert, wenn du eine elegantere lösung schon fast auf den lippen hattest ?

Ilja

[Matthias Jütte]

» Hattest du angenommen, dass durch den Löschbefehl die bereits ermittelte Ergebnismenge der anderen Query beeinflusst wird?
Nein, scheinbar einfach nicht lange genug nachgedacht.

Da brauchen wir wohl doch etwas Rauch... :-)

Hab' mal weiter geraucht, Umschreiben in "... AND id > $id" sollte es dann aber doch tun. Zur Sicherheit müßte die Auswahl davor dann auch noch um "ORDER BY id ASC" ergänzt werden.

In diesem Forum zählt eher der Lerneffekt auf dem Weg dahin :-)

Vorgehensweise in diesem Thread dabei mal wieder vorbildlich. Gibt's für mich nochwas zu lernen?

Gruß

Matthias

[dedlfix]

echo $begrüßung;

Hab' mal weiter geraucht, Umschreiben in "... AND id > $id" sollte es dann aber doch tun. Zur Sicherheit müßte die Auswahl davor dann auch noch um "ORDER BY id ASC" ergänzt werden.

Das sieht schon besser aus. Das ORDER BY id ist aber nicht nötig. Das id > $id ist ausreichend.

Gibt's für mich nochwas zu lernen?

Deine Lösung ist zwar jetzt funktional, aber leider sehr unperformant. Das wirst du bei einem größeren Datenbestand feststellen. Für jeden Datensatz wird eine DELETE-Abfrage an die DB gesendet. Das kostet. Besser ist es, die Löschkandidaten mittels (möglichst nur) eines Statements zu ermitteln, und dann vom Script einen einzigen Lösch-Befehl erstellen zu lassen: WHERE id IN (...)

echo "$verabschiedung $name";

[Ilja]

yo,

Ich möchte aus meiner Datenbanktabelle "newsletter" alle Einträge löschen, bei der der Wert der Spalte "email" doppelt vorkommt.

du musst erst einmal festlegen, welchen der doppelten datensätze du behalten willst und welche du löschen wills, zum besipiel gibt es ein datum, nachdem man sich richten kann, ala lösche mir den ältesten oder jüngsten.

Ist das mit MySQL überhaupt zu realisieren?

welche version von mysql benutzt du den ?

Ilja

[Max Power]

Ich hab gerade in einer Newsgroup gelesen, dass das mit MySQL nur über Umwege (Datei exportieren/importieren etc.) funktioniert ...
http://groups.google.de/group/de.comp.datenbanken.mysql/browse_frm/thread/8fcc7d4020b2ba7c/4886246aacafa9e8?tvc=1&q=MySQL+doppelte+Daten+l%C3%B6schen&hl=de#4886246aacafa9e8

@Ilja: MySQL: 3.23
... und welchen ich lösche, ist im meinem Fall egal! Hauptsache es gibt keine Doppelten mehr!

Max

[fanelf.de]

Ich möchte aus meiner Datenbanktabelle "newsletter" alle Einträge löschen, bei der der Wert der Spalte "email" doppelt vorkommt.
Ist das mit MySQL überhaupt zu realisieren?

mit mysql geht das ebenso wie mit jeder anderen sql db.
da der titel aber 'mit phpmyadmin' ist wie folgt:

1. phpmyadmin starten
2. query SELECT MAX(id) as id, email, COUNT(*) as anzahl FROM tabelle WHERE anzahl > 1 GROUP BY email

dann zeigt phpmyadmin alle sätze mit mehrfachen email an. in dieser liste kann man dann links vor jedem satz eine checkbox setzen und unten auf das X für löschen klicken.

[Ilja]

yo,

2. query SELECT MAX(id) as id, email, COUNT(*) as anzahl FROM tabelle WHERE anzahl > 1 GROUP BY email

dann zeigt phpmyadmin alle sätze mit mehrfachen email an. in dieser liste kann man dann links vor jedem satz eine checkbox setzen und unten auf das X für löschen klicken.

da habe ich meine zweifel, da durch die aggregat-funktion MAX(id), nur jeweils eine (maximale) id pro gleicher email angezeigt wird. deine lösung funktioniert nur, wenn es pro email maximal zwei datensätze gibt. es können aber durchaus 4 oder 5 datensätze mit gleicher email-adresse vorkommen.

Ilja

[fanelf.de]

da habe ich meine zweifel, da durch die aggregat-funktion MAX(id), nur jeweils eine (maximale) id pro gleicher email angezeigt wird. deine lösung funktioniert nur, wenn es pro email maximal zwei datensätze gibt. es können aber durchaus 4 oder 5 datensätze mit gleicher email-adresse vorkommen.

dann muss man halt pypMyAdmin mehrmals abschicken, bis nix mehr übrig bleibt. geht wahrscheinlich immer noch schneller als das andere.

[Ilja]

yo,

dann muss man halt pypMyAdmin mehrmals abschicken, bis nix mehr übrig bleibt. geht wahrscheinlich immer noch schneller als das andere.

nein, wenn du weiter unten lesen würdest, dann sage ich mal haben dedlfix und ich quasi in teamarbeit ein select gefunden, dass all die datensätze selektiert, die erhalten werden sollen. und dann ist es nicht weiter schwierig, alle anderen zu löschen.

Ilja