Doppelte Datensätze löschen

D

DJLopez

Mitglied
Hallo,

ich habe eine Datenbanktabelle, in die Daten importiert werden. Jeder Datensatz hat u.a. einen Timestamp. Es kann sein, dass die Daten, die importiert werden teilweise bereits vorhanden sind (doppeltes Importieren von Daten zB). Somit wären dann doppelte Datensätze - bis auf die (auto_increment) ID - absolut identisch. Die kann man nun die doppelten bzw. mehrfach vorhandenen Datensätze bis auf einen löschen?

Beispiel:

ID - timestamp - name - weitere daten...

1 - 1188905855 - name1 - weitere daten
2 - 1187905877 - name2 - weitere daten
3 - 1188905855 - name1 - weitere daten
4 - 1187905877 - name2 - weitere daten

Die Daten sind in dem Beispiel doppelt importiert worden, und nach dem import müssten die doppelten (oder mehrfachen) Einträge herausgefiltert werden.

Ich nutze hier MySQL 4.1.10a

Merci...
 
Hallo DJLopez,

folgeses Statement liefert die IDs der jeweils neuesten Datensätze pro Name:
SQL: 
SELECT
  CAST(SUBSTRING(MAX(CONCAT(LPAD(`timestamp`, '16', '0'), `id`)), 17) AS UNSIGNED)
FROM
  `tabelle`
GROUP BY
  `name`

Damit kann man nun entweder alle Sätze auswählen, die überflüssig sind (nämlich alle deren ID nicht in dieser Auswahl vorkommt):
SQL: 
SELECT
  *
FROM
  `tabelle`
WHERE
  `id` NOT IN (
                SELECT
                  CAST(SUBSTRING(MAX(CONCAT(LPAD(`timestamp`, '16', '0'), `id`)), 17) AS UNSIGNED)
                FROM
                  `tabelle`
                GROUP BY
                  `name`
              )

Oder man haut diese gleich in den Müll:
SQL: 
DELETE FROM
  `tabelle`
WHERE
  `id` NOT IN (
                SELECT
                  CAST(SUBSTRING(MAX(CONCAT(LPAD(`timestamp`, '16', '0'), `id`)), 17) AS UNSIGNED)
                FROM
                  `tabelle`
                GROUP BY
                  `name`
              )

Ist allerdings alles graue Theorie, habe nichts davon ausprobiert.
 
Zuletzt bearbeitet von einem Moderator:
Also das hat nicht funktioniert, MySQL geht in eine Endlosschleife und sogar der taskmanager braucht 5 minuten um zu starten. Böse Falle :)
 
Wollte das nicht auf mir sitzen lassen und habs ausprobiert. Und siehe da, tatsächlich: das DELETE ist so nicht erlaubt. "Die Verwendung der zu aktualisierenden Zieltabelle 'test' ist in der FROM-Klausel nicht zulässig." sagt mein MySQL. Na dann eben so:

Hier meine Beispieltabelle:
Code: 
+--+----------+------+
|id|zeit      |name  |
+--+----------+------+
|1 |1189011696|John  |
|2 |1189011702|Paul  |
|3 |1189011708|George|
|4 |1189011718|Ringo |
|5 |1189011728|John  |
|6 |1189011734|Paul  |*
|7 |1189011739|George|
|8 |1189011743|Ringo |
|9 |1189011746|John  |
|10|1189011748|George|
|11|1189011752|John  |
|12|1189011762|George|
|13|1189011764|John  |*
|14|1189011767|George|*
|15|1189011771|Ringo |*
+--+----------+------+
Die mit * markierten Zeilen sind die jeweils neuesten jedes Namens, das sind die, die erhalten bleiben sollen, alles andere kann weg. Richtig?

Dieses Statement:
SQL: 
SELECT
  CAST(SUBSTRING(MAX(CONCAT(LPAD(`zeit`, '16', '0'), `id`)), 17) AS UNSIGNED) AS `id`
FROM
  `test`
GROUP BY
  `name`
führt zu diesem Ergebnis:
Code: 
+--+
|id|
+--+
|14|
|13|
|6 |
|15|
+--+
also exakt die IDs der guten Datensätze.
Die müssen jetzt wohl zwischengespeichert werden. Da das ja ziemlich viele werden können (nehme ich mal an), eignet sich eine Temporäre Tabelle am besten. Also:
SQL: 
CREATE TEMPORARY TABLE `tmp_gute_ids` ENGINE=MEMORY
SELECT
  CAST(SUBSTRING(MAX(CONCAT(LPAD(`zeit`, '16', '0'), `id`)), 17) AS UNSIGNED) AS `id`
FROM
  `test`
GROUP BY
  `name`
(Ich glaube bei MySQL 4.1 muss es statt ENGINE=MEMORY noch TYPE=HEAP heißen.)

Jetzt Datensätze löschen:
SQL: 
DELETE FROM `test` WHERE `id` NOT IN (SELECT * FROM `tmp_gute_ids`)

Und dann noch aufräumen:
SQL: 
DROP TABLE IF EXISTS `tmp_gute_ids`

Das müsste jetzt eigentlich funktionieren. Bei mir gehts jedenfalls.
Sorry für die vorherige Falschmeldung, hätte mir eigentlich klar sein müssen dass man nicht gleichzeitig eine Tabelle abfragen kann während man aus ihr löscht.
 
Zuletzt bearbeitet von einem Moderator:
ManicMarble hat gesagt.:
Hier meine Beispieltabelle:
Code: 
+--+----------+------+
|id|zeit      |name  |
+--+----------+------+
|1 |1189011696|John  |
|2 |1189011702|Paul  |
|3 |1189011708|George|
|4 |1189011718|Ringo |
|5 |1189011728|John  |
|6 |1189011734|Paul  |*
|7 |1189011739|George|
|8 |1189011743|Ringo |
|9 |1189011746|John  |
|10|1189011748|George|
|11|1189011752|John  |
|12|1189011762|George|
|13|1189011764|John  |*
|14|1189011767|George|*
|15|1189011771|Ringo |*
+--+----------+------+
Die mit * markierten Zeilen sind die jeweils neuesten jedes Namens, das sind die, die erhalten bleiben sollen, alles andere kann weg. Richtig?
Zum Vergrößern anklicken....

:-( Das ist mir schon etwas peinlich - ich hab mich wohl nicht gut genug ausgedrückt :(

Es geht darum, dass Datensätze evtl _ganz_ doppelt vorkommen - ausser der ID, weil die ja beim importieren neu vergeben wird. Timestamp und name sind dabei die zwei Kriterien, mit denen man die doppelten rausfischen kann. Die Beispieltabelle würde also so aussehen:

Code: 
+--+----------+------+
|id|zeit      |name  |
+--+----------+------+
|1 |1189011696|John  |
|2 |1189011702|Paul  |
|3 |1189011708|George|
|4 |1189011718|Ringo |
|5 |1189011728|John  |
|6 |1189011734|Paul  |
|7 |1189011739|George|
|8 |1189011743|Ringo |
|9 |1189011746|John  |
|10|1189011748|George|
|11|1189011752|John  |
|12|1189011762|George|
|13|1189011764|John  |
|14|1189011767|George|
|15|1189011771|Ringo |
|16 |1189011696|John  |*
|17 |1189011702|Paul  |*
|18 |1189011708|George|*
|19 |1189011718|Ringo |*
|20 |1189011728|John  |*
+--+----------+------+

Die * gekennzeichneten Datensätze sind doppelt, und müssen raus.

Wie gesagt, ist mir schon etwas peinlich, wenn ich mein Anliegen nicht korrekt vorbringen kann :-(
Auf jeden Fall schonmal vielen Dank für die Hilfe bis jetzt!
 
Das macht die Sache ja noch einfacher: anstatt dem ganzen CAST(SUBSTRING(MAX(CONCAT(LPAD(...-Kram reict dann einfach nur MAX(`id`) und bei GROUP BY muss noch der `timestamp` dazu.
 
Hallo Tutorianer,

ich habe so gesagt das selbe Problem und kriege es auch mit den oben genannten Sachen nicht zum laufen. Vielleicht liegt es an NAvicat. Ich weiß es nicht. Gibt es eigentlich auch eine andere Methode ohne extra Tabellen zu erstellen ?

liebe Grüße
Willi
 
Temporäre Tabellen? Wuzu denn das.

Ist doch einfach. Du willst alle löschen ausser der ersten id der gefunden Gruppen (eine Gruppe kann auch aus nur 1 Datensatz bestehen)

SQL: 
DELETE
	dat.*
FROM
	double_data AS dat
	-- Alle ersten Ids per Gruppe auslesen
	LEFT JOIN (
		SELECT
			MIN(id) AS first_id
		FROM
			double_data
		GROUP BY
			--Gruppieren nach allen Feldern die als "Doppelter Datensatz"-Argument zählen
			value1,
			value2
		) AS sel	
		ON sel.first_id = dat.id
WHERE
	--Filtern damit er nur die Ids nimmt, die nicht im `sel` Subquery sind
	first_id IS NULL;
 
Zuletzt bearbeitet von einem Moderator:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück