SQL Löschen aller doppelten Einträge bis auf einem

[hemorieder]

Hey,

ich habe ein DB in der Einträge teilweise 40 mal vorkommen.
Alle unterscheiden sich nur anhand der einmaligen ID.
Nun würde ich gerne alle Datensätze löschen die mehrfach vorkommen, ein Wert soll jedoch bestehen bleiben.

Ich hätte es eigentlich so gemacht, aber das würde ja dafür sorgen, dass gar kein Wert mehr mehr bestehen bleibt.
Insgesamt reden wir von ca 100.000 Datensätzen.

delete from mydb p1
where exists (select *
             from mydb p2
             where p2.datum = p1.datum
               and p2.code = p1.code
               and p2.farbe = p1.farbe);

Jemand eine Idee?
Ein PHP skript auf dem Server könnte ich ausführen.

 

[benneq]

Jemand eine Idee?

Dieselbe wie immer: Google nutzen

https://www.javatpoint.com/mysql-delete-duplicate-records
https://www.mysqltutorial.org/mysql-delete-duplicate-rows/
https://phoenixnap.com/kb/mysql-remove-duplicate-rows

 

[hemorieder]

Hey, das hatte ich auch schon. Dein zweites Ergebnis war jedoch nicht dabei, danke.
Problem, es geht um genau 126.885.753 Datensätze, die Abfrage über PhyMyAdmin erzeugt immer dies:

#1205 - Beim Warten auf eine Sperre wurde die zulässige Wartezeit überschritten. Bitte versuchen Sie, die Transaktion neu zu starten

Es werden bis zu diesem Zeitpunkt jedoch keine Daten gelöscht. Wie umgehe ich das am besten, kann man irgendwie in Blöcken löschen?

 

[burglar225]

https://www.mysqltutorial.org/mysql-limit.aspx

 

[hemorieder]

klappt mit delete nicht

 

[paccoderpster]

Wenn du uns deine SQL-Anweisung zeigst, können wir schauen, ob was an dem Statement nicht stimmt. DELETE kann grundsätzlich mit LIMIT arbeiten (https://www.techonthenet.com/mysql/delete_limit.php)

 

[benneq]

Es ist eine Transaktion: Entweder wird der Befehl komplett ausgeführt oder gar nicht. Du müsstet also den Timeout verlängern, oder ganz umgehen indem du das Query z.B. via ssh direkt auf der Datenbank ausführst

 

[SucukAndEggs]

Hi

Du gruppierst über deine Tabelle und setzt Min oder Max auf deine Id die nur einmal vorkommt.
Ungefähr so: Select Min(id), Name, Adresse From Table Group by Name,Adresse
Damit hast du die, die du behalten möchtest.
Und alle die diese Id nicht haben, kannst du löschen.
Delete From Tabelle Where Id not in (Select min(id) From Table Group by name,.... )

 

[AW4]

Es ist eine Transaktion: Entweder wird der Befehl komplett ausgeführt oder gar nicht. Du müsstet also den Timeout verlängern, oder ganz umgehen indem du das Query z.B. via ssh direkt auf der Datenbank ausführst

Hast du keinen direkten Zugriff oder/und könntest auch nicht das Timeout entsprechend erhöhen (oder wenn dir die Kenntnisse dazu fehlen), bliebe dir noch die Möglichkeit das mit PHP in einzelnen Schritten zu bewerkstelligen:

1. Alle doppelt und mehrfachen Entitäten gruppiert selektieren, inkl. einer Min-Aggregation auf die ID.
2. Dieses Query von Anfang bis Ende einzeln durcharbeiten und dabei mit einem zweiten Delete-Query jeweils die minimale ID aus der Aggregation stehen lassen.

 

[hemorieder]

Wenn du uns deine SQL-Anweisung zeigst, können wir schauen, ob was an dem Statement nicht stimmt. DELETE kann grundsätzlich mit LIMIT arbeiten (https://www.techonthenet.com/mysql/delete_limit.php)

delete p1 from db p1
inner join db p2
where p1.id > p2.id and p2.datum = p1.datum and  p2.lk = p1.lk and  p2.inz = p1.inz
limit 10

 

[sandreas]

Wenn du eine solche Menge Datensätze bearbeiten willst:

• Falls noch nicht geschehen: Mach erstmal ein Backup - das sieht nicht aus, als wäre das eine Test-Datenbank
• Schränke für einen ersten Test die Ergebnismenge zusätzlich ein (in deinem Fall würde ich es mal über das Datum versuchen) um:
  • Die Ausführung des Queries zu beschleunigen (dann klappts vielleicht auch mit phpmyadmin ohne Timeout)
  • Den potenziellen "Schaden" gering zu halten...

Beispiel (Das Datum ist von mir völlig willkürlich gewählt - du solltest es auf einen Wert anpassen, der für deine Daten Sinn macht)...

delete p1 from db p1
inner join db p2
where p1.id > p2.id and p2.datum = p1.datum and p1.datum < '2018-10-02' p2.lk = p1.lk and  p2.inz = p1.inz
limit 10

Wenn du erfolgreich warst, dann das Filter-Datum Schritt für Schritt erhöhen, um die restlichen Datensätze zu löschen...

 

[paccoderpster]

@sandreas Ich hoffe, dass der TE mit Transaktionen arbeitet. Bei >100 Mio. Datensätzen würde ich sehr vermuten, dass das ein produktives System ist und die von dir genannten Tipps hoffentlich umgesetzt worden sind.

@hemorieder
Bei solch einem DELETE mit mehreren Tabellen geht in der Tat kein Limit. Ohne Limit ist die SQL-Syntax korrekt und sollte das machen, was du willst. Ich meine, dass ein INNER JOIN ohne ON ein Kreuzprodukt erzeugt. Das sprengt bei >100 Mio. Datensätzen den Server. Vielleicht hilft dir das?

DELETE p1
FROM db AS p1
INNER JOIN db AS p2
ON p2.datum = p1.datum AND p2.lk = p1.lk AND p2.inz = p1.inz
WHERE p1.id > p2.id

Leider ist SQL schon länger her als mir lieb ist. Aber vielleicht hilft es dir, wenn nicht, bin ich leider mit meinem Wissen am Ende.