[SQL] Dubbele records verwijderen uit een table

[ADT_Phantom]

Hallo,

Ik zit met het volgende probleem:

Ik heb een tabel "pk" en dit is een koppeltabel die er als volgt uitziet:

Veld Type
p int primary key (unique)
f1 int foreign key
f2 int foreign key
waarde varchar

Nu is deze tabel over de jaren behoorlijk vervuild geraakt door slechte code (die ik nu heb aangepast), maar nu zitten er nog veel records tussen die dubbel zijn, maar waarvan de primary key verschillend is.
vb:

p f1 f2 waarde

1 1 1 hallo
2 1 1 hallo
3 1 2 iets
4 1 2 iets
5 2 1 iets
6 2 2 hallo

In het voorbeeld wil ik dus de records met p=2 en p=4 verwijderen aangezien deze dus dubbel zijn.
Nu heb ik al gegoogled en daarbij heb ik nog niet veel succes gehad. Ik zoek dus een query waarmee ik alle dubbele records kan verwijderen en dit wil ik niet handmatig doen aangezien er meer dan 1,7 miljoen records in de tabel zitten. Kan iemand mij helpen?

[whoami]

Heb je zelf al eens bedacht hoe die query er kan uitzien ? Wat heb je zelf al geprobeerd, want dit lijkt hirer toch verdacht veel op een scriptrequest, en dit laten we niet toe.
heb je al eens gekeken naar de aggregate functions zoals COUNT en het GROUP BY statement ?

[ADT_Phantom]

Ja, ik heb al vanalles gepobeerd met count en having. Ik heb de volgende query al in elkaar geknutseld, maar deze werkt niet.:

SELECT p
FROM pk
WHERE f1 IN (
SELECT f1
FROM pk
GROUP BY f1
HAVING (COUNT(f1) > 1)
)
AND
f2 IN (
SELECT f2
FROM pk
GROUP BY f2
HAVING (COUNT(f2) > 1)
)
AND
waarde IN (
SELECT waarde
FROM pk
GROUP BY waarde
HAVING (COUNT(waarde) > 1)
)
ORDER BY p

[Verwijderd]

en al eens bedacht dat de ROWID voor elke record uniek is (ook als alle andere waarden identiek zijn)? Misschien dat je daar iets mee kan

[bitMeister]

Volgens mij zou je zoiets kunnen proberen

SELECT f1, f2, count(1)
FROM ...
GROUP BY f1, f2
HAVING count(1) > 1

Dit geeft je een list van alle dubbele values. Dit stop je in een tijdelijke tabel.
Om p te vinden doe je (g is je goede tabel, t tijdelijk)

SELECT max( t.p )
FROM ... t, .... g
WHERE t.f1 = g.tf1 and t.f2=g.tf2

Dit stop je dan met een update statement in the tijdelijk tabel

Dan haal je alle records in je goede tabel weg waar je overeenkomstige records in je tijdelijke tabel hebt.

Misschien moet je dit een aantal keren doen als er meer dan twee identieke records voorkomen (maar dat is niet waarschijnlijk)

(1e stmnt aangepast)

[ Voor 6% gewijzigd door bitMeister op 15-02-2007 04:33 ]

[jvdmeer]

Indien je subqueries mag gebruiken kan je deze gebruiken:

SQL:

DELETE FROM pk WHERE ID in
(
SELECT MAX(p) FROM pk GROUP BY f1, f2, waarde HAVING COUNT(*)>1
)

Eventueel meerdere keren draaien om ook 3-voudige duplicaten te verwijderen.

[ Voor 27% gewijzigd door jvdmeer op 15-02-2007 09:16 . Reden: Opmerking over meerdere keren starten ]

[MicroWhale]

Waarom zet je er geen constraint op zodat die dubbele records niet eens ingevoerd kunnen worden?

[ Voor 9% gewijzigd door MicroWhale op 15-02-2007 09:15 ]

[Verwijderd]

misschien wil hij dat juist gaan doen.... maar dan moet je eerst de dubbele eruit gooien.

[MicroWhale]

Verwijderd schreef op donderdag 15 februari 2007 @ 09:17:
misschien wil hij dat juist gaan doen.... maar dan moet je eerst de dubbele eruit gooien.

dat zou best wel eens kunnen ja

Dus:
1. nieuwe tabel aanmaken mèt constraint,
2. alles overpompen (dubbele vallen eruit door constraint),
3. oude verwijderen en nieuwe op plek oude zetten.

[ADT_Phantom]

Bedankt allemaal, ik heb de manier van MrWilliams gebruikt en ik heb nu een table zonder de dubbele waarden. Door de constraint die er nu op zit komen er dus ook geen dubbele waarden meer in.

[Verwijderd]

code:

  delete from pk where rowid not in (select min(rowid) from pk group by F1, F2)

humz, beetje laat he species wel makkelijker (voor de volgende keer)

[ Voor 29% gewijzigd door Verwijderd op 15-02-2007 09:38 ]

[Verwijderd]

Een truuk die ik een vriend van me een keer heb aangeraden (nuttig als je een versie van MySQL gebruikt waarin je nog geen subqueries mag gebruiken):

code:

DELETE t1.* 
FROM pk t1
INNER JOIN pk t2 USING(f1, f2, waarde)
WHERE t1.p > t2.p;

Oftewel: Join de tabel met zichzelf en verwijder van alle combinaties van records diegene met de hogere primary key. Zo hou je van alle dubbelen alleen die met de laagste primary key over.

[ Voor 10% gewijzigd door Verwijderd op 15-02-2007 13:59 ]

[Verwijderd]

volgens mij is dat gewoon een herschrijving van mijn query

[TeeDee]

Verwijderd schreef op donderdag 15 februari 2007 @ 13:17:
volgens mij is dat gewoon een herschrijving van mijn query

Yup, maar deze is _zonder_ subqueries. Handig, als je MySQL geen subqueries snapt.
Maar goed, dat zegt OneOfBorg ook.

[ Voor 6% gewijzigd door TeeDee op 15-02-2007 13:22 ]

[Verwijderd]

Verwijderd schreef op donderdag 15 februari 2007 @ 09:36:

code:

  delete from pk where rowid not in (select min(rowid) from pk group by F1, F2)

De query van jvdmeer was beter. "where in" werkt over het algemeen sneller dan "where not in".

[Verwijderd]

Dat valt tegn denk ik, zijn query moet je namelijk in het geval van n dubbele records ook n-1 keer uitvoeren, en die van mij is in één keer klaar. Zeker als je niet weet hoeveel keer een dubbel record tegenkomt gaat dat flink in je nadeel werken.
Daarnaast, als je weinig dubbelen hebt, hoeft hij maar een vergelijk met een kleine collectie te doen waardoor het performanceverlies te verwaarlozen is door het gebruik van de NOT IN

[jvdmeer]

Verwijderd schreef op donderdag 22 februari 2007 @ 08:55:
Dat valt tegn denk ik, zijn query moet je namelijk in het geval van n dubbele records ook n-1 keer uitvoeren, en die van mij is in één keer klaar. Zeker als je niet weet hoeveel keer een dubbel record tegenkomt gaat dat flink in je nadeel werken.

Dit zou wel eens kunnen kloppen, echter:

Verwijderd schreef op donderdag 22 februari 2007 @ 08:55:
Daarnaast, als je weinig dubbelen hebt, hoeft hij maar een vergelijk met een kleine collectie te doen waardoor het performanceverlies te verwaarlozen is door het gebruik van de NOT IN

Dit klopt niet, want bij 1 miljoen records met 3 dubbelen zou

code:

  delete from pk where rowid not in (select min(rowid) from pk group by F1, F2)

999997 regels opleveren om mee te vergelijken. Beter is dan:

code:

  delete from pk where rowid not in (select min(rowid) from pk group by F1, F2 having count(*)>1)

Maar al met al is de oplossing van OneOfBorg de mooiste:

code:

DELETE t1.* 
FROM pk t1
INNER JOIN pk t2 USING(f1, f2, waarde)
WHERE t1.p > t2.p;

en de beste.

[Verwijderd]

is inderdaad een mooie oplossing

Ik zat nog even naar mijn query te kijken, daar is nog wel ruimte voor verbetering. Bijvoorbeeld door de key mee te nemen in de subquery waardoor je de onderliggende index van de constraint kan gebruiken (tenminste, bij Oracle zit die eronder, anders kan je 'm handmatig maken) en je niet meer de hele tabel door hoeft te grazen .