# Doppelte Datensätze löschen



## DJLopez (4. September 2007)

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...


----------



## ManicMarble (4. September 2007)

Hallo DJLopez,

folgeses Statement liefert die IDs der jeweils neuesten Datensätze pro Name:

```
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):

```
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:

```
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.


----------



## DJLopez (5. September 2007)

Also das hat nicht funktioniert, MySQL geht in eine Endlosschleife und sogar der taskmanager braucht 5 minuten um zu starten. Böse Falle


----------



## ManicMarble (5. September 2007)

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:

```
+--+----------+------+
|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:

```
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:

```
+--+
|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:

```
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:

```
DELETE FROM `test` WHERE `id` NOT IN (SELECT * FROM `tmp_gute_ids`)
```

Und dann noch aufräumen:

```
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.


----------



## DJLopez (6. September 2007)

ManicMarble hat gesagt.:


> Hier meine Beispieltabelle:
> 
> ```
> +--+----------+------+
> ...



:-( 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:


```
+--+----------+------+
|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!


----------



## ManicMarble (6. September 2007)

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.


----------



## Willi_Kaiser (6. Juni 2011)

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


----------



## Yaslaw (6. Juni 2011)

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)


```
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;
```


----------

