Dubletten zählen...

[Mark]

Hallo,

gibt es eine schnelle und effektive Möglichkeit, z.B. schon innerhalb der Select-Anweisung doppelte Einträge zu zählen und ggfls zu entfernen?

Eine Tabelle meiner DB sieht so aus:

ID   Datum   Quellnr  Zielnr
...  .....   .......  ......

Jetzt ist es möglich, dass einige Zeilen bei den Feldern Quellnr und Zielnr gleiche Inhalte haben- die Zeilen würden sich nur in den Spalten ID (autoincwert) und Datum (Datetime) unterscheiden.
Diese Dubpletten möchte ich rausfiltern und entfernen.

Wer weiß wie man da am besten vorgeht?

[Vinzenz Mai]

Hallo,

gibt es eine schnelle und effektive Möglichkeit, z.B. schon innerhalb der Select-Anweisung doppelte Einträge zu zählen

ja.

und ggfls zu entfernen?

nein.

ID   Datum   Quellnr  Zielnr
...  .....   .......  ......

Jetzt ist es möglich, dass einige Zeilen bei den Feldern Quellnr und Zielnr gleiche Inhalte haben- die Zeilen würden sich nur in den Spalten ID (autoincwert) und Datum (Datetime) unterscheiden.

Diese Dubpletten möchte ich rausfiltern

Suche:
   Wähle die Spalten Quellnr und Zielnr aus,
   zähle das Auftreten,
   gruppiert nach Quellnr und Zielnr.
   Betrachte nur die Datensätze, die mehr als einmal vorkommen.

übersetzt in SQL:

SELECT  
    Quellnr,  
    Zielnr,  
    COUNT(*) Anzahl       -- ein hübscher Name, um die Spalte besser  
                          -- ansprechen zu können.  
FROM  
    tabelle  
GROUP BY  
    Quellnr,  
    Zielnr  
HAVING  
    COUNT(*) > 1  

Um die ID-Werte zu ermitteln, kannst Du ein Subselect verwenden.

und entfernen.

Das ist schwieriger. Welchen Datensatz möchtest Du bestehen lassen.

Wer weiß wie man da am besten vorgeht?

Unbedingt vorher ein Backup erstellen.

Freundliche Grüße

Vinzenz

[Mark]

Hallo!

Danke für das Beispiel! Die Ausgabe der doppelten/dreifachen Datensätze funktioniert prima.
Ich könnte doch nunmehr theoretisch in einer do while schleife alle Datensätze löschen, die durch das select vorher entsprechend erfaßt wurden, oder?
Welcher der doppelten gelöscht wird ist egal. Und das feld ID (autoinc) ist dabei doch gut als Schlüssel zu gebrauchen, denke ich...

Backup? Klar, mach ich!

[Vinzenz Mai]

Hallo,

Danke für das Beispiel! Die Ausgabe der doppelten/dreifachen Datensätze funktioniert prima.
Ich könnte doch nunmehr theoretisch in einer do while schleife alle Datensätze löschen, die durch das select vorher entsprechend erfaßt wurden, oder?
Welcher der doppelten gelöscht wird ist egal. Und das feld ID (autoinc) ist dabei doch gut als Schlüssel zu gebrauchen, denke ich...

das Feld ID bekommst Du durch das Verfahren nicht.
Du bekommst in einem zweiten Schritt alle betroffenen ID-Werte.
In einem dritten Schritt könntest Du alle außer den kleinsten (oder größten) ID-Werten ermitteln.

Schreibe die ID-Werte aller Mehrfacheinträge - außer der kleinsten ID eines Mehrfacheintrages - in eine temporäre Tabelle.
Erstelle ein Backup der Originaltabelle.
Lösche aus der Originaltabelle alle Datensätze mit einer ID, die in der temporären Tabelle aufgeführt ist.

Ach so, ich hatte etwas völlig vergessen: mit PHP hat das ganze nichts zu tun.

Freundliche Grüße

Vinzenz

[Blubb]

Hallo,

Diese Dubpletten möchte ich rausfiltern und entfernen.
Wer weiß wie man da am besten vorgeht?

Neben den Lösungsvorschlägen zum Löschen von doppelten Einträgen: mit einem besseren Tabellendesign.
Ich weiß zwar nicht welches dein(e) Primärschlüssel ist/sind aber offensichtlich hängen weder 'Quellnr' noch 'Zielnr' von ID ab. Falls diese beiden Werte unabhängig von einander sind, würde es sich anbieten zwei Tabellen zu benutzen. Eine für Quellnr und eine für Zielnr. Dann können keine "doppelten" (Im Sinne von Quellnr = Zielnr) Einträge mehr vorkommen.

Grüße

[Benjamin Buxbaum]

Hi Mark!

Eine Tabelle meiner DB sieht so aus:

ID   Datum   Quellnr  Zielnr
...  .....   .......  ......

Jetzt ist es möglich, dass einige Zeilen bei den Feldern Quellnr und Zielnr gleiche Inhalte haben- die Zeilen würden sich nur in den Spalten ID (autoincwert) und Datum (Datetime) unterscheiden.
Diese Dubpletten möchte ich rausfiltern und entfernen.

delete from deine_tabelle  
where id in (  
select distinct b.id  
from deine_tabelle a, deine_tabelle b  
where a.id < b.id  
and a.quellnr = b.quellnr  
and a.zielnr = b.zielnr )

Davon ausgehend, dass die ID der primary key ist, sollte es das sein. Überprüfe das aber gründlich, bevor Du es blind laufen lässt! ;-)

mfG
Benjamin

[Vinzenz Mai]

Hallo Benjamin,

delete from deine_tabelle

where id in (
select distinct b.id
from deine_tabelle a, deine_tabelle b
where a.id < b.id
and a.quellnr = b.quellnr
and a.zielnr = b.zielnr )

  
vom Standardfall MySQL ausgehend (Mark hat's leider nicht angegeben):  
  
<[zitat](http://dev.mysql.com/doc/refman/5.0/en/delete.html)>  
    Currently, you cannot delete from a table and select from the  
    same table in a subquery.  
</zitat>  
  
  
Freundliche Grüße  
  
Vinzenz

[Mark]

Ja, es ist eine Sql-Datenbank.

...Heißt das jetzt, dass dein Beispiel zum löschen funktionieren müßte?

[Vinzenz Mai]

Hallo,

Ja, es ist eine Sql-Datenbank.

heißt das Datenbankmanagementsystem (DBMS) MySQL? Es gibt nämlich eine ganze Reihe von Datenbankmanagementsystemen, die SQL (einigermaßen) verstehen:

DB2, Firebird, Informix, MS SQL-Server, MySQL, Oracle, PostgreSQL, SQLite, ... um nur mal ein paar zu nennen.

...Heißt das jetzt, dass dein Beispiel zum löschen funktionieren müßte?

Benjamins Code wird unter MySQL nicht ausgeführt :-) Du könntest seine Anweisung leicht abwandeln, um eine temporäre Tabelle mit den zu löschenden IDs zu füllen. Im zweiten Schritt die Datensätze löschen, die Entsprechungen in der temporären Tabelle haben, wie ich bereits schrieb.

Anderes DBMS, anderes Glück.

Freundliche Grüße

Vinzenz

[Benjamin Buxbaum]

Hi Vinzenz!

vom Standardfall MySQL ausgehend (Mark hat's leider nicht angegeben):

<zitat>
    Currently, you cannot delete from a table and select from the
    same table in a subquery.
</zitat>

Interessant. In Oracle geht das. ;-) Dann wird es wohl ein wenig komplizierter in MySQL. Ist es möglich ein Select als Tabelle zu "missbrauchen"? Also so etwas:

select * from  
(select a from b)  
where a = x

Ich nehme an, das geht auch nicht.

mfG
Benjamin

[EKKi]

Mahlzeit Benjamin Buxbaum,

Ist es möglich ein Select als Tabelle zu "missbrauchen"? Also so etwas:

select * from

(select a from b)
where a = x

  
Das hat mit missbrauchen wenig zu tun ... je nach Version unterstützt auch MySQL Sub-Selects.  
  
  
MfG,  
EKKi  

-- 
sh:( fo:| ch:? rl:( br:> n4:~ ie:% mo:} va:) de:] zu:) fl:{ ss:) ls:& js:|