Löschen doppelte Zeilen in MySQL

1. Löschen Sie doppelte Zeilen mit der Anweisung DELETE JOIN
2. Doppelte Zeilen mit verschachtelter Abfrage löschen
3. Löschen Sie doppelte Zeilen mithilfe einer temporären Tabelle
4. Löschen doppelte Zeilen mit der Funktion ROW_NUMBER()

Dieser Artikel zeigt Ihnen die zahlreichen Möglichkeiten, doppelte Zeilen in der Tabelle in MySQL zu löschen. Es gibt vier verschiedene Ansätze, um diese Aufgabe zu erfüllen.

1. Löschen Sie doppelte Zeilen mit der Anweisung DELETE JOIN
2. Löschen Sie doppelte Zeilen mit Nested Query
3. Löschen Sie doppelte Zeilen mithilfe einer temporären Tabelle
4. Löschen Sie doppelte Zeilen mit der Funktion ROW_NUMBER()

Das folgende Skript erstellt eine Tabelle customers mit vier Spalten (Kundennummer, Vorname, Nachname und E-Mail).

CREATE TABLE customers (custid INT,  first_name VARCHAR(50) NOT NULL,  last_name  VARCHAR(50) NOT NULL,  email VARCHAR(255) NOT NULL );
CREATE TABLE customers (custid INT,  first_name VARCHAR(50) NOT NULL,  last_name  VARCHAR(50) NOT NULL,  email VARCHAR(255) NOT NULL );

Fügen Sie für die Demonstration Beispieldatenwerte in die Tabelle customers ein.

INSERT INTO customers  
VALUES (110,'Susane','Mathew','sussane.mathew@gmail.com'),
       (124,'Jean','Carl','jean.carl@gmail.com'),
       (331,'Peter','cohelo','peter.coh@google.com'),
       (114,'Jaine','Lora','jaine.l@abs.com'),
       (244,'Junas','sen','jonas.sen@mac.com');

INSERT INTO customers  
VALUES (113,'Jaine','Lora','jaine.l@abs.com'),
       (111,'Susane','Mathew','sussane.mathew@gmail.com'),
       (665,'Roma','Shetty','roma.sh11@yahoo.com'),
       (997,'Beatrice','shelon','beatrice.ss22@yahoo.com'),
       (332,'Peter','cohelo','peter.coh@google.com');

Hinweis: Damit können Sie nach dem Löschen wieder Werte einfügen.

Unten ist die angegebene Abfrage, die alle Daten aus der Tabelle customers zurückgibt:

SELECT * FROM customers order by custid;

Um nach doppelten Datensätzen aus der Tabelle zu suchen, führen wir die unten erwähnte Abfrage in der Tabelle customers aus.

SELECT custid, COUNT(custid) FROM customers GROUP BY  custid HAVING  COUNT(custid) > 1; 

Wie Sie sehen, haben wir in unseren Ergebnissen drei Zeilen mit doppelter Kundennummer.

Löschen Sie doppelte Zeilen mit der Anweisung DELETE JOIN

Die Verwendung von INNER JOIN mit der Delete-Anweisung ermöglicht es Ihnen, doppelte Zeilen aus Ihrer MySQL-Tabelle zu entfernen.

Die folgende Abfrage verwendet das Konzept der verschachtelten Abfrage, indem alle Zeilen ausgewählt werden, die doppelte Datensätze mit der niedrigsten Kunden-ID aufweisen. Einmal gefunden, löschen wir diese doppelten Datensätze mit der niedrigsten custid:

Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid < c2.custid AND c1.email = c2.email);

Die Kundentabelle wird in dieser Abfrage zweimal referenziert; daher verwendet es den Alias ​​c1 und c2.

Die Ausgabe wird sein:

Es zeigte an, dass drei Zeilen gestrichen wurden.

SELECT custid, COUNT(custid) FROM customers GROUP BY  custid HAVING  COUNT(custid) > 1; 

Diese Abfrage gibt nun einen leeren Satz zurück, was bedeutet, dass die doppelten Zeilen gelöscht wurden.

Die Daten aus der Tabelle customers können wir mit der Abfrage select verifizieren:

SELECT * FROM customers;

Falls Sie doppelte Zeilen löschen und die niedrigste custid beibehalten möchten, können Sie dieselbe Abfrage verwenden, jedoch mit geringfügigen Abweichungen in der Bedingung, wie in der folgenden Anweisung gezeigt:

Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid > c2.custid AND c1.email = c2.email);

Die folgende Ausgabe zeigt die Daten der Tabelle customers, nachdem doppelte Zeilen entfernt wurden.

Doppelte Zeilen mit verschachtelter Abfrage löschen

Sehen wir uns nun die schrittweise Vorgehensweise zum Entfernen doppelter Zeilen mit einer verschachtelten Abfrage an. Dies ist ein vergleichsweise einfacher Ansatz zur Lösung des Problems.

Zuerst wählen wir mit dieser Abfrage eindeutige Datensätze aus der Tabelle aus.

Select * from (select max(custid) from customers group by email);

Dann verwenden wir die Abfrage delete mit der Klausel where, wie unten gezeigt, um doppelte Zeilen in der Tabelle zu löschen.

Delete from customers where custid not in 
    (select * from (select max(custid) from customers group by email));

Die Ausgabe wird sein:

Löschen Sie doppelte Zeilen mithilfe einer temporären Tabelle

Sehen wir uns nun die schrittweise Vorgehensweise zum Entfernen doppelter Zeilen mithilfe einer temporären Tabelle an:

1. Zunächst müssen Sie eine neue Tabelle mit der gleichen Struktur wie die ursprüngliche Tabelle erstellen.
2. Fügen Sie nun verschiedene Zeilen aus der Originaltabelle in die temporäre Tabelle ein.
3. Löschen Sie die Originaltabelle und benennen Sie die temporäre Tabelle in der Originaltabelle um.

Schritt 1: Tabellenerstellung mit CREATE TABLE und LIKE Schlüsselwort

Syntax zum Kopieren der gesamten Struktur der Tabelle ist wie unten gezeigt.

CREATE TABLE destination_table LIKE source;

Angenommen, wir haben dieselbe Kundentabelle, schreiben wir die unten angegebene Abfrage.

CREATE TABLE temporary LIKE customers;

Schritt 2. Einfügen von Zeilen in eine temporäre Tabelle

Die unten angegebene Abfrage kopiert die eindeutige Zeile von den Kunden und schreibt sie in eine temporäre Tabelle. Hier gruppieren wir per E-Mail.

INSERT INTO temporary SELECT * FROM customers GROUP BY email; 

Schritt 3. Löschen Sie die ursprüngliche Kundentabelle und erstellen Sie eine temporäre Tabelle, die als Originaltabelle fungiert, indem Sie sie in Kunden umbenennen.

DROP TABLE customers;

ALTER TABLE temporary RENAME TO customers;

Die Ausgabe wird sein:

Dieser Ansatz ist zeitaufwändig, da er eine Änderung der Tabellenstruktur erfordert, anstatt nur an Datenwerten zu arbeiten.

Löschen doppelte Zeilen mit der Funktion ROW_NUMBER()

Die Funktion ROW_NUMBER() wurde in MySQL-Version 8.02 eingeführt. Sie können diesen Ansatz also wählen, wenn Sie eine MySQL-Version höher als 8.02 verwenden.

Diese Abfrage weist jeder Zeile mit der Funktion ROW_NUMBER() einen numerischen Wert zu. Bei doppelten E-Mails ist die Zeilennummer größer als eins.

SELECT  custid, email, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email ) AS row FROM customers;

Das obige Code-Snippet gibt eine ID-Liste der doppelten Zeilen zurück:

SELECT custid
FROM ( SELECT custid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS row FROM customers) t WHERE row > 1;

Sobald wir die Liste der Kunden mit doppelten Werten erhalten haben, können wir diese mit der Anweisung delete mit Unterabfrage in der where-Klausel löschen, wie unten gezeigt.

DELETE FROM customers
WHERE custid IN 
(SELECT custid  FROM 
     (SELECT custid, ROW_NUMBER() OVER
                (PARTITION BY email ORDER BY email) AS row FROM customers) t
                      WHERE row > 1);

Die Ausgabe wird sein: