Kalle_B: MySQL: Doppelte rows löschen

Hallöle,

in einer Tabelle sind Datensätze mehrfach vorhanden. Ich möchte alle Duplikate löschen.

Die Kombination adr_id / slot_nr darf nur einmal vorkommen, es muss ein UNIQUE- Key werden.

Aber wie werfe ich jetzt Satz 2, 3, 4, ... raus und Satz 1 bleibt stehen?

Lieben Gruß, Kalle

  1. Dubletten identifiziert (und ggf. killt) man mit einer Unterabfrage auf eine "SELECT DISTINCT"-Abfrage. Die Dubletten sind nicht in der "SELECT DISTINCT"-Abfrage enthalten.

  2. SELECT adr\_id, slot\_nur, count(*)
    FROM tabelle
    GROUP BY adr\_id, slot\_nr
    HAVING count(*) > 1

    wenn es nicht zu viele Sätze sind kannst du diese dann einzeln löschen. ansonsten einfach diese sätze querkopieren in eine temporäre tabelle und dann einfach löschen.

    1. SELECT adr\_id, slot\_nur, count(*)
      FROM tabelle
      GROUP BY adr\_id, slot\_nr
      HAVING count(*) > 1

      Im Prinzip gehts so:

        
      DELETE  
      FROM  
       tabelle  
      WHERE  
       (  
       tabelle_id NOT IN  
        (  
        SELECT DISTINT  
         tabelle_id,  
         tabelle_beispieldatenfeld       -- das Dublettenkritium, können auch mehrere sein  
        FROM  
         tabelle  
        )  
       )  
      
      
      1. Kleine Korrektur:

          
        DELETE  
        FROM  
         tabelle  
        WHERE  
         (  
         tabelle_id NOT IN  
          (  
          SELECT  
           Temp.tabelle_id  
          FROM  
           (  
           SELECT DISTINT  
            tabelle_id,  
            tabelle_beispieldatenfeld       -- das Dublettenkritium, können auch mehrere sein  
           FROM  
            tabelle  
           ) Temp  
          )  
         )  
        
        

        Vielleicht gehts einfacher...   ;)

  3. yo,

    Aber wie werfe ich jetzt Satz 2, 3, 4, ... raus und Satz 1 bleibt stehen?

    du musst ein kriterium auswählen, wonach du entscheiden kannst, welcher datensatz drinne bleibt und welcher nicht. das könnte man anhand eines datums machen, welche älter oder neuer ist oder aber auch anhand des pk-schlüssels.

    dein dbms muss dafür unterabfragen unterstützen, das geht bei mysql ab 4.1. in meinem beispiel lösche ich alle dubletten, deren pk-schlüssel größer ist, als das "orginal", das erhalten bleiben soll.

    DELETE FROM tabelle t1
    WHERE t1.pk_spalte IN
     (SELECT t2.pk_spalte
      FROM t2
      WHERE t2.adr_id = t1.adr_id
      AND   t2.slot_nr = t2.slot_nr
      AND   t2.pk_spalte > t1.pk_spalte
     )

    Ilja

    1. Hallo, Ilja,

      mein SQL- Kommando zickt. Kann MySQL 4.1 evtl den subselect nicht?

      Ab wann geht der?

      DELETE FROM tm_anwesenheit AS anw1
      WHERE anw1.id IN
       (SELECT anw2.id
        FROM  tm_anwesenheit AS anw2
        WHERE anw2.adr_id  = anw1.adr_id
        AND   anw2.slot_nr = anw1.slot_nr
        AND   anw2.id      > anw1.id
       )

      MySQL meldet:
      #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 'WHERE anw1.id IN
      (SELECT anw2.id
        FROM  tm_anwesenheit AS anw2
        WHERE anw2' at line 2

      1. mein SQL- Kommando zickt. Kann MySQL 4.1 evtl den subselect nicht?

        Ab wann geht der?

        http://dev.mysql.com/doc/refman/5.1/de/subqueries.html
        "Seit MySQL 4.1 werden alle Unterabfrageformen und -operationen, die der SQL-Standard vorsieht, ebenso unterstützt wie einige weitere Funktionen, die MySQL-spezifisch sind."

        DELETE FROM tm_anwesenheit AS anw1
        WHERE anw1.id IN
        (SELECT anw2.id
          FROM  tm_anwesenheit AS anw2
          WHERE anw2.adr_id  = anw1.adr_id
          AND   anw2.slot_nr = anw1.slot_nr
          AND   anw2.id      > anw1.id
        )

        Vielleicht ein "AS" zuviel?

      2. yo,

        mein SQL- Kommando zickt. Kann MySQL 4.1 evtl den subselect nicht?

        4.1 kann subselects und ich kann auch erst einmal keinen fehler erkennen. bist du sicher, dass dein dbms 4.1 ist und nicht nur der client ? scheint mir ein problem der version zu sein.

        Ilja

        1. yo,

          ich bin ein schnösel, letzter satz der seite bringt des rätsel lösung:

          http://dev.mysql.com/doc/refman/5.1/de/delete.html

          ausserdem war meine anweisung meiner meinung nach auch nicht ganz richtig. so wie es aussieht, muss du dir erst einmal eine hilfstabelle erstellen und diese dann zum löschen der doubletten benutzen.

          CREATE TABLE loeschen
          AS
          (SELECT MIN(id), adr_id, slot_nr
           FROM tm_anwesenheit
           GROUP BY adr_id, slot_nr
           HAVING COUNT(*) > 2
          )

          das erzeugt eine tabelle, mit der angabe der doppelten datensätze und der kleinsten id, die also nicht zu löschen ist, sondern alle, deren werte bei adr_id und slot_nr sich gleichen und größer der id sind.

          und das ist auch das, was Rafael gesagt hat. ich meine mich zu erinnern, dass es nicht das erste mal ist, dass ich die doublettenprüfung versemmel...

          Ilja

          1. Man sollte natürlich schon:

            • auf temporäre Tabellen verzichten
            • auf problematische Grössenvergleiche der ID, die ja nicht numerisch sein muss, verzichten
            • die Umsetzung mengenbasiert realisieren und ganz sicher keine Einzellöschungen vornehmen, wie von Rafael vorgeschlagen