So entfernen Sie doppelte Datensätze in SQL

Müssen Sie SQL verwenden, um Duplikate in Ihren Tabellen zu entfernen? In diesem Artikel erfahren Sie, wie Sie eine SQL-Abfrage schreiben, um doppelte Daten zu entfernen.

Inhaltsverzeichnis

Umschalten

Das Problem – Duplikate in SQL entfernen
Zusammenfassung der Methoden
Methode 1 – ROW_NUMBER analytische Funktion
Methode 2: Löschen mit JOIN
Methode 3 – MIN- oder MAX-Funktion
Methode 4 – DENSE_RANK
Methode 5 – Korrelierte Unterabfrage mit MIN oder MAX
Methode 6: Verwenden einer Unterabfrage mit ANY
Andere Methoden, auf die Sie stoßen könnten
Methode 7: Verwenden einer Zwischentabelle
Schlussfolgerung

Das Problem – Duplikate in SQL entfernen

Nehmen wir an, Sie haben eine Tabelle mit einigen Daten. Sie haben festgestellt, dass diese Tabelle einige doppelte Daten enthält. Und Sie möchten die Duplikate loswerden.

Die Art und Weise, wie Sie doppelte Daten definieren, kann von Ihren Daten abhängen.

Handelt es sich um ein Duplikat, wenn alle Spalten gleich sind?
Handelt es sich um ein Duplikat, wenn alle Spalten mit Ausnahme des Primärschlüssels identisch sind?
Handelt es sich um ein Duplikat, wenn nur wenige Spalten gleich sind?

In jedem Fall ist das Identifizieren und Entfernen von Duplikaten in SQL möglich.

Es gibt mehrere Möglichkeiten, dies zu tun. Ich werde jede dieser Methoden erklären.

Wir werden sehen, wie es auf Oracle, SQL Server, MySQL und PostgreSQL funktionieren kann.

Die Datenstichprobe enthält 1.220 Datensätze in einer einzelnen Tabelle, die wie folgt aussieht:

customer_id	first_name	last_name	Adresse
1	'Bamby'	'Applin'	29 Lighthouse Bay Lane
2	'Blinny'	'Kirman'	96024 Jenifer Zentrum
3	'Rodie'	'Dils'	62729 Debs Point
4	'Abdel'	'Grisedale'	711 711 3000 1000-
5	'Fernanda'	"Ljochin"	54 Dorton Platz

Angenommen, ein Datensatz ist ein Duplikat, wenn er die gleichen first_name und last_name Werte enthält.

Werfen wir einen Blick auf die verschiedenen Möglichkeiten, Duplikate in SQL zu entfernen.

Zusammenfassung der Methoden

Hier finden Sie eine Zusammenfassung der verschiedenen Methoden und der Datenbanken, mit denen sie arbeiten.

Methode	Orakel	SQL Server	MySQL (MySQL)	PostgreSQL
ROW_NUMBER	Y	Y	Y	Y
Löschen mit Join	Y	Y	Y	Y
MIN oder MAX	Y	Y	Y	Y
DENSE_RANK	Y	Y	N	Y
Korrelierte Unterabfrage mit MIN oder MAX	Y	N	N	N
Unterabfrage mit ANY	Y	N	N	N

Ein Hinweis zu Abfragezeiten

In jedem dieser Beispiele erläutere ich den Code, den ich verwende, was er tut, und lösche Daten mit der DELETE-Anweisung.

Die Abfragezeiten sollten jedoch nur als Richtwerte dienen und können sich von der Leistung unterscheiden, die Sie erhalten:

Die Tabellenstruktur, die eindeutigen Datengeschäftsregeln und die Anzahl der Datensätze unterscheiden sich
Ihre Datenbankversion und Spezifikationen unterscheiden sich
Mehrere andere Faktoren können sich auf die Leistung auswirken, z. B. der Abfragecache oder Hintergrundprozesse auf dem Computer.

Also, ich denke, mein Punkt ist, dass Sie vielleicht nicht die gleiche Leistung erzielen wie ich. Wenn Sie jedoch eine Methode ausprobieren und die Leistung für Ihre Daten schlecht ist, versuchen Sie es mit einer der anderen Methoden.

Methode 1 – ROW_NUMBER analytische Funktion

Datenbank: Oracle, MySQL, SQL Server, PostgreSQL

Die erste Methode, die ich Ihnen zeige, ist die Verwendung einer Analysefunktion namens ROW_NUMBER. Es wurde an mehreren Stellen empfohlen, z. B. bei StackOverflow-Fragen und in einem AskTOM-Thread.

Es umfasst mehrere Schritte:

Verwenden einer Unterabfrage zum Suchen jedes ROWID (d. h. einer eindeutigen Nummer, die jeder Zeile in einer Oracle-Tabelle zugewiesen wird) und der ROW_NUMBER-Funktion, um eine fortlaufende Nummer für diese Zeile zu finden, die nach den Feldern gruppiert ist, die Sie als eindeutig angeben.
Suchen Sie die ROWID-Werte, die als Duplikate identifiziert wurden.
Löschen Sie Zeilen, die mit diesen ROWIDs übereinstimmen.

Die Abfrage sieht folgendermaßen aus:

DELETE FROM table a

WHERE a.ROWID IN

(SELECT ROWID FROM

(SELECT

ROWID,

ROW_NUMBER() OVER

(PARTITION BY unique_columns ORDER BY ROWID) dup

FROM table)

WHERE dup > 1);

Die ROW_NUMBER Funktion wird hier als analytische Funktion verwendet. Es verwendet PARTITION BY, um Partitionen oder Gruppen basierend auf den Feldern zu erstellen, die ich in PARTITION BY erwähnt habe (d. h. first_name, last_name und created_date). Ich habe sie dann bei der ROWID bestellt.

Dies bedeutet, dass das erste Vorkommen dieser Felder die Zahl 1 erhält. Das zweite Vorkommen all dieser Felder erhält die Zahl 2 und so weiter.

Die ROWIDs werden dann an die DELETE-Anweisung am Anfang zurückgegeben, die nur Datensätze löscht, bei denen die ROW_NUMBER Funktion (die in diesem Beispiel den Alias "dup" hat) größer als eins ist. (Der AskTOM-Thread verwendet "WHERE dup <> 1", erreicht aber dasselbe).

Es empfiehlt sich, die zu löschenden Datensätze zuerst zu überprüfen, indem Sie eine SELECT-Anweisung mit diesen Kriterien ausführen. Für die Zwecke dieser Beispiele überprüfe ich die ANZAHL der Datensätze, die gelöscht werden sollen, indem DELETE durch ein SELECT COUNT(*) ersetzt wird.

SELECT COUNT(*)

FROM customer a

WHERE a.ROWID IN

(SELECT ROWID FROM

(SELECT

ROWID,

ROW_NUMBER() OVER

(PARTITION BY first_name, last_name, address

ORDER BY ROWID) dup

FROM customer)

WHERE dup > 1);

Ergebnis:

220

Es wird angezeigt, dass 220 doppelte Datensätze vorhanden sind.

In MySQL oder anderen Datenbanken kann Ihre Abfrage wie folgt aussehen:

SELECT COUNT(*)

FROM customer a

WHERE a.customer_id IN

(SELECT customer_id FROM

(SELECT

customer_id,

ROW_NUMBER() OVER

(PARTITION BY first_name, last_name, address

ORDER BY customer_id) dup

FROM customer)

WHERE dup > 1);

Wenn ich diesen Befehl als DELETE-Anweisung ausführe:

DELETE FROM customer a

WHERE a.ROWID IN

(SELECT ROWID FROM

(SELECT

ROWID,

ROW_NUMBER() OVER

(PARTITION BY first_name, last_name, address) dup

FROM customer)

WHERE dup > 1);

Ergebnis:

220 Datensätze gelöscht.

Dies ist die Methode, die ich verwenden würde, wenn ich doppelte Datensätze aus einer Tabelle löschen müsste. Es verwendet einen ROWID-Filter, der normalerweise eine schnelle Möglichkeit ist, auf eine Tabelle zuzugreifen.

Methode 2: Löschen mit JOIN

Datenbank: Oracle, SQL Server, MySQL, PostgreSQL

Dies ist eine häufig empfohlene Methode für MySQL und funktioniert für alle anderen Datenbanken. Dazu müssen Sie dieselbe Tabelle mit sich selbst verknüpfen, die übereinstimmenden Spalten angeben und alle bis auf eine doppelte Zeile löschen.

Hier ist die Beispielabfrage:

DELETE t1

FROM customer t1

INNER JOIN customer t2

ON t1.customer_id < t2.customer_id

AND t1.first_name = t2.first_name

AND t1.last_name = t2.last_name

AND t1.address = t2.address;

Wir geben die Kundentabelle zweimal an. Wir geben die Felder first_name, last_name und Adresse an, die die doppelte Zeile identifizieren.

Wir geben auch customer_id < customer_id an, wodurch alle Kunden mit Ausnahme des Kunden mit der höchsten ID gelöscht werden.

Wenn wir diese Abfrage ausführen, erhalten wir dieses Ergebnis.

Ergebnis:

220 Zeilen gelöscht.

Sie können die Abfrage so ändern, dass > anstelle von < verwendet wird, wenn Sie die Zeilen mit der niedrigsten ID beibehalten möchten.

Methode 3 – MIN- oder MAX-Funktion

Datenbank: Oracle, SQL Server, MySQL, PostgreSQL

Diese Methode verwendet entweder die MIN- oder die MAX-Funktion, um Duplikate in einer Unterabfrage zu finden.

Es ähnelt früheren Beispielen, verwendet jedoch weniger Unterabfragen.

Diese Methode funktioniert nur, wenn Sie für jede Zeile einen eindeutigen Wert haben. Wenn es einige doppelte Werte gibt (z. B. wenn die ID für die Zeile dupliziert ist), funktioniert es nicht.

In diesem Beispiel wird die MIN-Funktion verwendet, aber Sie können die MAX-Funktion verwenden, um das gleiche Ergebnis zu erhalten.

Die Syntax sieht folgendermaßen aus:

DELETE FROM your_table

WHERE rowid not in (

SELECT MIN(rowid)

FROM your_table

GROUP BY column1, column2, column3

);

Die Spalten in der GROUP BY-Anweisung sind diejenigen, die auf Duplikate überprüft werden. In dem Beispiel, das ich verwendet habe, sind dies die first_name, last_name und created_date.

Wenn ich es zuerst als SELECT COUNT(*) ausführe, kann ich die Anzahl der betroffenen Zeilen sehen.

SELECT COUNT(*)

FROM customer

WHERE rowid NOT IN (

SELECT MIN(rowid)

FROM customer

GROUP BY first_name, last_name

);

Ergebnis:

220 Reihen.

Jetzt kann ich die DELETE-Anweisung ausführen.

DELETE FROM customer

WHERE rowid NOT IN (

SELECT MIN(rowid)

FROM customer

GROUP BY first_name, last_name

);

Ergebnis:

220 Zeilen gelöscht.

Hinweis: Diese Art von Abfrage kann in MySQL ausgeführt werden, zeigt jedoch falsche Ergebnisse an. Dies liegt daran, dass MySQL kein Äquivalent zu ROWID hat, bei dem es sich um einen eindeutigen Wert für jede Zeile handelt.

Methode 4 – DENSE_RANK

Datenbank: Oracle, SQL Server, PostgreSQL

Nicht: MySQL

Diese Methode ähnelt Methode 1, verwendet jedoch die DENSE_RANK-Methode .

Tatsächlich ist es fast genau dasselbe.

Die Syntax sieht folgendermaßen aus:

DELETE FROM table a

WHERE a.ROWID IN (

SELECT rid FROM (

SELECT ROWID rid,

DENSE_RANK() OVER(

PARTITION BY unique_columns

ORDER BY ROWID) dup

FROM table

)

WHERE dup > 1

);

Es verwendet das gleiche Konzept wie ROW_NUMBER, verwendet jedoch die DENSE_RANK-Funktion. Es gibt einige Unterschiede zwischen der Verwendung dieser Funktionen, aber in diesem Fall liefern sie die gleiche Ausgabe.

Lassen Sie uns zuerst die ANZAHL(*) der Datensätze ermitteln.

SELECT COUNT(*) FROM customer

WHERE ROWID IN (

SELECT rid FROM (

SELECT ROWID rid,

DENSE_RANK() OVER(

PARTITION BY first_name, last_name

ORDER BY ROWID) dup

FROM customer

)

WHERE dup > 1

);

Ergebnis:

220 Reihen.

Hier wird die gleiche Anzahl von Datensätzen wie bei den Methoden 1 und 2 angezeigt.

Versuchen wir es nun mit der DELETE-Anweisung.

DELETE FROM customer

WHERE ROWID IN (

SELECT rid FROM (

SELECT ROWID rid,

DENSE_RANK() OVER(

PARTITION BY first_name, last_name

ORDER BY ROWID) dup

FROM customer

)

WHERE dup > 1

);

Ergebnis:

220 Zeilen gelöscht.

Dadurch werden alle gefundenen doppelten Datensätze gelöscht.

Bei dieser Methode können Sie anstelle von DENSE_RANK auch eine RANK-Funktion verwenden. Es sollte die gleichen Ergebnisse zeigen.

Methode 5 – Korrelierte Unterabfrage mit MIN oder MAX

Datenbank: Oracle

Nicht: MySQL, SQL Server, PostgreSQL

Die nächste Methode, die ich vorstellen werde, ähnelt Methode 4, verwendet jedoch eine korrelierte Unterabfrage, um Spalten abzugleichen.

DELETE

FROM table a

WHERE ROWID NOT IN (

SELECT MAX(ROWID)

FROM table b

WHERE a.col1 = b.col1

AND a.col2 = b.col2

AND a.col3 = b.col3

);

Sie ähnelt der vorherigen Abfrage, aber anstelle einer GROUP BY-Klausel verwenden wir eine WHERE-Klausel. Diese WHERE-Klausel verknüpft die Tabelle innerhalb der Unterabfrage mit der Tabelle außerhalb der Unterabfrage. Die Tabellen werden in den übereinstimmenden Spalten verknüpft.

Schauen wir uns an, wie viele Datensätze gefunden werden.

SELECT COUNT(*)

FROM customer a

WHERE ROWID NOT IN (

SELECT MAX(ROWID)

FROM customer b

WHERE a.first_name = b.first_name

AND a.last_name = b.last_name

);

Ergebnis:

220 Reihen.

Lassen Sie uns dies nun als DELETE-Abfrage ausführen:

DELETE

FROM customer a

WHERE ROWID NOT IN (

SELECT MAX(ROWID)

FROM customer b

WHERE a.first_name = b.first_name

AND a.last_name = b.last_name

);

Ergebnis:

220 Zeilen gelöscht.

Methode 6: Verwenden einer Unterabfrage mit ANY

Datenbank: Oracle

Nicht: MySQL, SQL Server, PostgreSQL

Die nächste Methode, die wir uns ansehen werden, ist die Verwendung einer Unterabfrage zum Identifizieren und Löschen doppelter Daten.

Ich zeige Ihnen zuerst die Abfrage und erkläre dann, wie sie funktioniert.

DELETE FROM tablename a

WHERE a.rowid > ANY (

SELECT b.rowid

FROM tablename b

WHERE a.column1 = b.column1

);

Nun, es gibt ein paar Dinge zu beachten, die bei dieser Abfrage zu beachten sind.

Sowohl die Unterabfrage als auch die äußere Abfrage sollten aus derselben Tabelle auswählen, aber unterschiedliche Tabellenaliase verwenden. In diesem Beispiel habe ich a und b verwendet.

Die WHERE-Klausel in der Unterabfrage wird verwendet, um zu definieren, wie Sie ein Duplikat identifizieren. Sie schreiben die WHERE-Klausel, die für jede Spalte verknüpft wird, die Sie beim Identifizieren von Duplikaten berücksichtigen. Dabei kann es sich um ein Feld, zwei Felder oder jedes Feld in der Tabelle handeln.

Die rowid-Pseudospalte wird verwendet, um Zeilen in der Unterabfrage zu identifizieren, da diese garantiert eindeutig ist.

Die WHERE-Klausel der äußeren Abfrage verwendet eine > ANY-Bedingung, um nach Duplikaten zu suchen. Es werden alle Zeilen gelöscht, deren Zeilen-ID größer ist als mindestens eine andere Zeile. Dadurch wird sichergestellt, dass alle Zeilen bis auf eine erfüllt sind, die Ihren Bedingungen entsprechen, und somit werden alle Duplikate entfernt.

Wie funktioniert diese Methode?

Die Verwendung dieser Abfrage für unsere Beispieldaten sieht wie folgt aus:

DELETE

FROM customer a

WHERE a.rowid > ANY (

SELECT b.rowid

FROM customer b

WHERE a.first_name = b.first_name

AND a.last_name = b.last_name

AND a.address = b.address

);

Wenn wir dies als SELECT COUNT(*) ausführen, können wir die Anzahl der betroffenen Datensätze sehen:

SELECT COUNT(*)

FROM customer a

WHERE a.rowid > ANY (

SELECT b.rowid

FROM customer b

WHERE a.first_name = b.first_name

AND a.last_name = b.last_name

AND a.address = b.address

);

Ergebnis:

ANZAHL(*)

220

Diese Abfrage hat 220 doppelte Werte gefunden. Es ist dasselbe wie in Beispiel 1.

Lassen Sie es uns nun als DELETE-Abfrage ausführen.

DELETE

FROM customer a

WHERE a.rowid > ANY (

SELECT b.rowid

FROM customer b

WHERE a.first_name = b.first_name

AND a.last_name = b.last_name

AND a.address = b.address

);

Das Ergebnis ist:

220 Zeilen gelöscht.

Andere Methoden, auf die Sie stoßen könnten

Ich habe nur ein paar verschiedene Möglichkeiten gefunden, doppelte Daten in SQL zu löschen. Ich habe unten noch ein paar weitere aufgelistet, die nicht so gut funktionieren wie die anderen, wenn überhaupt.

Ich habe diese hinzugefügt, damit Sie sehen können, ob bestimmte Methoden funktionieren, z. B. wenn jemand bei der Arbeit eine Methode erwähnt, die er ausprobiert hat. Oder wenn Sie eine Methode gefunden haben, die auf SQL Server funktioniert, und sie in einer Oracle-Datenbank ausprobieren möchten.

Hier sind also die anderen Methoden, die ich gefunden habe, um doppelte Daten zu löschen.

Nur um es klar zu sagen, diese funktionieren nicht, und sie sind nur als Referenz hier.

Methode 7: Verwenden einer Zwischentabelle

Diese Methode unterscheidet sich ein wenig von den anderen, da wir mehrere Schritte verwenden. Wir erstellen eine Tabelle, um die deduplizierten Daten zu speichern, und aktualisieren dann die Haupttabelle damit.

Hier sind die Schritte:

Erstellen Sie eine neue Tabelle, die die gleiche Struktur wie die ursprüngliche Tabelle aufweist.
Einfügen der unterschiedlichen (deduplizierten) Zeilen aus der ursprünglichen Tabelle in die neue Tabelle
Löschen der ursprünglichen Tabelle
Benennen Sie die neue Tabelle in die ursprüngliche Tabelle um

So machen wir das.

Zuerst erstellen wir eine neue Tabelle mit der gleichen Struktur:

CREATE TABLE customer_int AS

SELECT customer_id, first_name, last_name, address

FROM customer

WHERE 1=0;

Als Nächstes füllen wir die neue customer_int Tabelle mit eindeutigen Datensätzen auf, indem wir SELECT und GROUP BY verwenden:

SELECT customer_id, first_name, last_name, address

FROM customer

GROUP BY first_name, last_name, address;

Dadurch wird jedoch eine Fehlermeldung generiert:

Fehlercode: 1055. Ausdruck #1 der SELECT-Liste ist nicht in der GROUP BY-Klausel enthalten und enthält die nicht aggregierte Spalte 'test.customer.customer_id', die funktional nicht von Spalten in der GROUP BY-Klausel abhängig ist. Dies ist inkompatibel mit sql_mode=only_full_group_by

Es verursacht einen Fehler aufgrund einer falschen Verwendung von GROUP BY.

Dies mag in früheren Versionen von MySQL funktioniert haben, aber nicht in neueren Versionen. Andere Datenbanken können ähnliche Fehler aufweisen.

Möglicherweise gibt es eine Möglichkeit, eine Zwischentabelle mit einer anderen Methode aufzufüllen, aber es ist möglicherweise einfacher, die Datensätze mit einer anderen Methode in diesem Handbuch zu löschen.

Methode 8 – LÖSCHEN, WHERE IN

Diese Methode verwendet eine WHERE-Klausel mit angegebenen Spalten, die mit einer Unterabfrage abgeglichen werden.

Es scheint Methode 4 sehr ähnlich zu sein, mit der Hinzufügung der drei Spalten anstelle von ROWID.

Die Methode sieht folgendermaßen aus:

DELETE FROM table WHERE

(col1, col2, col3) IN (

SELECT MAX(col1), col2, col3

FROM table

GROUP BY col2, col3

);

Hier ist die Abfrage in unserer Datenbank:

DELETE FROM customer

WHERE (customer_id, first_name, last_name, address) IN (

SELECT MAX(customer_id), first_name, last_name, address

FROM customer

GROUP BY first_name, last_name, address

);

In Oracle habe ich diese Abfrage einige Minuten lang ausgeführt, dann aber beendet, ohne ein Ergebnis zu erhalten. Ich kann mir vorstellen, dass es wegen der WHERE-Klausel für die drei Felder und nicht wegen der zuvor erwähnten ROWID langsam läuft.

In MySQL habe ich dies ausgeführt und eine Fehlermeldung erhalten:

Fehlercode: 1093. Sie können die Zieltabelle "customer" nicht für die Aktualisierung in der FROM-Klausel angeben

Dies liegt daran, dass Sie die Tabelle nicht in der FROM-Klausel und in der WHERE-Klausel angeben können.

Diese Abfrage kann für Sie funktionieren, wenn eines der Felder, die Sie betrachten, eine ID oder eine Art von Zahl ist (um den MAX-Wert abzurufen) und wenn Sie Indizes für diese Felder haben.

Aber für mich hat diese Abfrage nicht funktioniert.

Methode 9 – LÖSCHEN mit LEFT OUTER JOIN

Diese Methode verwendet eine LEFT OUTER JOIN von der Tabelle, die die zu löschenden Datensätze enthält, zu einer Unterabfrage, die die doppelten Datensätze enthält.

DELETE (

SELECT d.*, d.rowid

FROM customer d

LEFT OUTER JOIN (

SELECT MIN(RowId) AS MinRowId,

first_name, last_name, address

FROM customer

GROUP BY first_name, last_name, address

) KeepRows ON d.RowId = KeepRows.MinRowId

WHERE KeepRows.MinRowId IS NULL;

Schauen wir uns zuerst die ANZAHL an:

SELECT COUNT(*)

FROM customer

LEFT OUTER JOIN (

SELECT MIN(RowId) AS MinRowId,

first_name, last_name, address

FROM customer

GROUP BY first_name, last_name, address

) KeepRows ON all_customer_dupes.RowId = KeepRows.MinRowId

WHERE KeepRows.MinRowId IS NULL;

ANZAHL(*)

176

Es wird eine falsche Anzahl doppelter Datensätze angezeigt. Wir könnten dies als DELETE-Befehl auf SQL Server ausführen und die Zeilen werden gelöscht.

Wenn wir Oracle verwenden, können wir versuchen, dies als DELETE-Befehl auszuführen.

DELETE (

SELECT d.*, d.rowid

FROM customer d

LEFT OUTER JOIN (

SELECT MIN(RowId) AS MinRowId,

first_name, last_name, address

FROM customer

GROUP BY first_name, last_name, address

) KeepRows ON d.RowId = KeepRows.MinRowId

WHERE KeepRows.MinRowId IS NULL

Ergebnis:

SQL-Fehler: ORA-01752: Kann ohne genau eine schlüsselerhaltene Tabelle01752 nicht aus der Ansicht gelöscht werden01752. 00000 – "kann ohne genau eine schlüsselerhaltene Tabelle nicht aus der Ansicht gelöscht werden"*Ursache: Die gelöschte Tabelle enthielt – keine schlüsselerhaltenen Tabellen,– mehr als eine schlüsselerhaltene Tabelle, oder
– die schlüsselerhaltene Tabelle war eine nicht zusammengeführte Ansicht.*Aktion: Definieren Sie die Ansicht neu oder löschen Sie sie aus den zugrunde liegenden Basistabellen.

Diese Abfrage hat bei mir nicht funktioniert und diesen Fehler auf Oracle angezeigt.

Methode 10 – DELETE mit WITH-Klausel

Diese Methode basiert auch auf einem Konzept, das in SQL Server als CTE (Common Table Expressions) bezeichnet wird.

Die Abfrage sieht folgendermaßen aus:

WITH cte AS

(SELECT ROW_NUMBER()

OVER (PARTITION BY first_name, last_name, address

ORDER BY (customer_id)) AS RN

FROM customer)

DELETE FROM cte

WHERE rn > 1;

In SQL Server können Sie diese Abfrage ausführen, und Zeilen werden gelöscht.

220 Zeilen gelöscht.

Wenn wir in Oracle diese Abfrage ausführen, erhalten wir eine Fehlermeldung:

ORA-00928: fehlendes SELECT-Schlüsselwort00928. 00000 – "fehlendes SELECT-Schlüsselwort"*Ursache:*Aktion:

Dies liegt daran, dass die WITH-Klausel in Oracle über eine SELECT-Abfrage verfügen muss, die auf sie folgt, und nicht über eine DELETE-Abfrage. Es funktioniert nicht mit DELETE-Anweisungen.

Methode 11 – Zwei Tabellen Joined

Bei dieser Methode werden zwei Tabellen verwendet, die in den übereinstimmenden Spalten miteinander verknüpft sind, und eine ROWID-Prüfung, um nur diejenigen zu finden, die eindeutig sind. Ich habe die zu löschende Tabelle nach dem DELETE-Schlüsselwort angegeben, damit die Datenbank weiß, aus welcher Tabelle ich löschen möchte.

DELETE (

SELECT t1.rowid

FROM all_customer_dupes t1

INNER JOIN all_customer_dupes t2

ON t1.first_name = t2.first_name

AND t1.last_name = t2.last_name

AND t1.created_date = t2.created_date

AND t1.rowid > t2.rowid

);

Versuchen wir zuerst, eine Zählung zu erhalten:

SELECT COUNT(t1.customer_id)

FROM all_customer_dupes t1

INNER JOIN all_customer_dupes t2

ON t1.first_name = t2.first_name

AND t1.last_name = t2.last_name

AND t1.created_date = t2.created_date

AND t1.rowid > t2.rowid;

Aufzeichnungen:

40.267 Zeilen.

Es zeigt die richtige Anzahl von Zeilen an. Versuchen wir nun, sie zu löschen.

DELETE (

SELECT t1.rowid

FROM all_customer_dupes t1

INNER JOIN all_customer_dupes t2

ON t1.first_name = t2.first_name

AND t1.last_name = t2.last_name

AND t1.created_date = t2.created_date

AND t1.rowid > t2.rowid

);

Ergebnis (in Oracle):

SQL-Fehler: ORA-01752: Es kann nicht aus der Ansicht gelöscht werden, ohne genau eine schlüsselerhaltene Tabelle01752. 00000 – "kann ohne genau eine schlüsselerhaltene Tabelle nicht aus der Ansicht gelöscht werden"*Ursache: Die gelöschte Tabelle hatte – keine schlüsselerhaltenen Tabellen,– mehr als eine schlüsselerhaltene Tabelle, oder – die schlüsselerhaltene Tabelle war eine nicht zusammengeführte Ansicht.*Aktion: Definieren Sie die Ansicht neu oder löschen Sie sie aus den zugrunde liegenden Basistabellen.

Es wird ein ähnlicher Fehler wie bei der vorherigen Methode angezeigt.

Dies liegt daran, dass Oracle zwei Tabellen in einer DELETE-Anweisung nicht unterstützt. Auch wenn wir angegeben haben, aus welcher wir löschen möchten, ist dies in Oracle nicht zulässig.

Methode 12 – Inline-Ansicht und Analysefunktion

Diese Methode ähnelt früheren Methoden, die funktionieren, verwendet jedoch eine Inlineansicht anstelle einer tatsächlichen Tabelle.

DELETE

FROM (SELECT c.*,

ROW_NUMBER()

OVER (PARTITION BY first_name, last_name, created_date

ORDER BY ROWID DESC) AS RN

FROM all_customer_dupes c

) sub

WHERE rn > 1;

Anstatt DELETE FROM all_customer_dupes zu sagen, lösche ich aus der Unterabfrage selbst.

Versuchen wir, die Anzahl der Datensätze zu zählen.

SELECT COUNT(*)

FROM (SELECT c.*,

ROW_NUMBER()

OVER (PARTITION BY first_name, last_name, created_date

ORDER BY ROWID DESC) AS RN

FROM all_customer_dupes c

) sub

WHERE rn > 1;

Ergebnis:

40.267 Zeilen.

Die Zählung ist korrekt. Versuchen wir nun einen Löschvorgang.

DELETE

FROM (SELECT c.*,

ROW_NUMBER()

OVER (PARTITION BY first_name, last_name, created_date

ORDER BY ROWID DESC) AS RN

FROM all_customer_dupes c

) sub

WHERE rn > 1;

Ergebnis in Oracle:

SQL-Fehler: ORA-01732: Datenmanipulationsvorgang ist in dieser Ansicht nicht zulässig01732. 00000 – "Datenmanipulationsvorgang aus dieser Sicht nicht zulässig"*Ursache:*Aktion:

Dieser Fehler tritt auf, weil Oracle keine Löschvorgänge für Inlineansichten wie diese zulässt. Sie müssen aus einer Ist-Tabelle löschen.

Wir erhalten einen ähnlichen Fehler mit MySQL.

Methode 13 – Verwenden Sie RANK und Primärschlüssel

Diese Methode ist eine weitere, die ich online gefunden habe und die nicht funktioniert:

DELETE FROM table

WHERE ROWID IN (

SELECT "rowid"

FROM (

SELECT "rowid", rank_n

FROM (

SELECT RANK() OVER (

PARTITION BY unique_columns ORDER BY rowid) rank_n,

rowid as "rowid"

FROM table

WHERE primary_key IN (

SELECT primary_key

FROM table

GROUP BY all_columns

HAVING COUNT(*) > 1

)

WHERE rank_n > 1

);

Diese Abfrage sieht so aus, als würde sie eine RANK-Funktion als Analysefunktion mit einem Primärschlüssel wie in früheren Beispielen verwenden, fügt jedoch eine Unterabfrage in der WHERE-Klausel hinzu. Diese Unterabfrage verursacht einen Fehler, da GROUP BY nicht mit Spalten verwendet werden kann, die nicht in der SELECT-Klausel enthalten sind.

Diese Funktion wird also nicht einmal ausgeführt, da sie Syntaxfehler aufweist.

Schlussfolgerung

Zusammenfassend lässt sich sagen, dass es einige Möglichkeiten gibt, doppelte Datensätze mit SQL aus Ihrer Tabelle zu löschen. Einige funktionieren für alle Datenbanken, andere nur für einige Datenbanken.