[sql] dubbele records verwijderen

[Suepahfly]

Ik wil dus dubbele records verwijderen uit een groote tabel met 42000+ records.

Op google heb ik heel oplossingen gevonden maar die werkten bij mj niet. Ik ben ook al de hele avond zo'n beetje bezig.

Een oplossing in php heb ik ook gezien, maar mijn servertje trekt het niet om 42471 resultaten tegen elkaar te vegelijken als array of variabelen.

Tabel structuur:

CREATE TABLE `gb01_countries` (
ìd`bigint(15) unsigned NOT NULL auto_increment
`ip_range_from` bigint(10) unsigned NOT NULL default '0',
`ip_range_to` bigint(10) unsigned NOT NULL default '0',
`country_code_short` char(2) NOT NULL default '',
`country_name` varchar(255) NOT NULL default '',
KEY `ip_range_from` (`ip_range_from`),
KEY `ip_range_to` (`ip_range_to`)
) TYPE=MyISAM;


Deze tabel bevat dat van www.ip-to-country.com
Volledig CSV bestand kan je hier gratis downloaden

Daarmee kan je ip adressen vergelijken tegen landen.

Querie die ik zelf heb geprobeerd, in meerdere variaties:

code:

DELETE
FROM gb01_guestbook
WHERE gb01_guestbook.id IN

#- List 1 - all rows that have duplicates
(SELECT gb01.id FROM gb01_countries AS gb01
WHERE Exists (SELECT ip_range_from, ip_range_to, Count(id)
FROM gb01_guestbook
WHERE gb01_guestbook.ip_range_from = gb01.ip_range_from
   AND gb01_guestbook.ip_range_to = gb01.ip_range_to
GROUP BY gb01_guestbook.ip_range_from, gb01_guestbook.ip_range_to
HAVING Count(gb01_guestbook.id) > 1))
AND gb01_guestbook.id NOT IN

#- List 2 - one row from each set of duplicate
(SELECT Min(id)
FROM gb01_countries AS gb01
WHERE Exists (SELECT Field1, Field2, Count(id)
FROM gb01_countries
WHERE gb01_countries.ip_range_from = gb01.ip_range_from
   AND gb01_countries.ip_range_to = gb01.ip_range_to
GROUP BY gb01_countries.ip_range_to, gb01_countries.ip_range_to
HAVING Count(gb01_countries.id) > 1)
GROUP BY ip_range_from, ip_range_to);

[gorgi_19]

Welke database heb je? MySQL? En zo ja, welke versie?

[Suepahfly]

MySQL 3.23.55

[djluc]

Kun je het niet beter met een self-join oplossen?

[PanMan]

Ik heb ook wel's met die lijst zitten spelen (nooit echt gebruikt, tho).
Wat je kan doen, is gewoon met een textverwerker sorten, in de CSV, en dan "remove doubles" doen. En dan pas in MySQL gooien, aangezien je toch eenmalig de dubbelen wilt verwijderen.
Ik wou het nu even voor je doen, maar volgens mij zitten er nu geen dubbels meer in? Toen ik er eerder mee speelde, zaten er idd. veel dubbelen in. Maar nu lijkt dat erg mee te vallen, ik zie ze zo niet.

[SuperRembo]

Is het niet simpeler om alle unieke records (al de records die je dus wel wil hebben) in een nieuwe tabel te stoppen?

[Verwijderd]

Of,.... ik weet niet of mySql dat aankan..., een view maken waarin je met distinct alle unieke records neerzet en vervolgens deze view met de originele tabel vergelijkt. Iets van:

[sql]
DELETE * FROM ORGTABEL
WHERE ID NOT IN (SELECT id FROM VIEWTABEL)
[/sql]

?

[Verwijderd]

Verwijderd schreef op 23 October 2003 @ 12:31:
Of,.... ik weet niet of mySql dat aankan..., een view maken waarin je met distinct alle unieke records neerzet en vervolgens deze view met de originele tabel vergelijkt. Iets van:

[sql]
DELETE * FROM ORGTABEL
WHERE ID NOT IN (SELECT id FROM VIEWTABEL)
[/sql]

?

Dit zal niet lukken aangezien het ID voor ieder record altijd uniek is en DISTINCT alsnog alle records retourneert, incl. de dubbele.
Mijn advies: gooi je auto-increment weg, maak een tweede tabel met dezelfde structuur (hier mag wel de auto increment in) en doe een select distinct into van je oude tabel naar je nieuwe en je hebt geen dubbele records meer.

[jvdmeer]

Ik heb 't document even doorgekeken, maar volgens Excel zitten er geen dubbelen in. Dus dan ben je snel klaar.

[Suepahfly]

Dan maar opnieuw downen. Scheelt een hoop werk volgens mij (Heb geen MS office, dan kon het met de wizard 'dubbele records' van Acces)

Want de vesie die ik heb heeft wel dubbele records (Italy o.a.)

Die heb ik trouwens eind vorige week gedownload.