[SQL] Duplicated records verwijderen

[robertpNL]

Tweakers,

Wat is de juiste manier om duplicated recors te verwijderen?

Stel, een table bestaat uit een primaire key (id) en een value (allebei int):

code:

id / value
1 / 132
2 / 133
3 / 134
4 / 133 (moet worden verwijderd)
5 / 133 (ook dit record moet worden verwijderd)
6 / 140

Nu moeten de records 4 en 5 worden verwijderd want 2 bevat al '133'.

Wat is de beste manier om deze duplicated records te verwijderen?

Een Store Procedure? Maar hoe moet de SP tewerk gaan? Records copieren naar een temp-table en met de SP controleren op duplicated records? Is dat niet performances vertragend?

Mijn kennis van SQL is verder basis, maar graag leer ik meer.

Bedankt alvast.

[whoami]

Ik zou eerst nagaan welke records er duplicated zijn.
Dit kan je doen dmv een eenvoudige query, waarbij je telt hoeveel keer iedere value voorkomt:

code:

select count(1) as aantal, value 
INTO #tmpTabel
from tabel group by value having count(1) > 1

Dit kan je in een temp table steken.

Wat je dan kan doen is je temp table dmv een cursor overlopen, en iedere keer n - 1 records (waarbij n het aantal keer is dat die value voorkomt) uit je tabel verwijderen.

Dit kan je in een stored procedure doen; maar welk DBMS gebruik je ?

Dit is één manier, maar wellicht zijn er nog efficiëntere.

[ Voor 3% gewijzigd door whoami op 26-08-2005 21:13 ]

[robertpNL]

whoami schreef op vrijdag 26 augustus 2005 @ 21:11:
Ik zou eerst nagaan welke records er duplicated zijn.
Dit kan je doen dmv een eenvoudige query, waarbij je telt hoeveel keer iedere value voorkomt:

code:

select count(1) as aantal, value 
INTO #tmpTabel
from tabel group by value having count(1) > 1

Dit kan je in een temp table steken.

Wat je dan kan doen is je temp table dmv een cursor overlopen, en iedere keer n - 1 records (waarbij n het aantal keer is dat die value voorkomt) uit je tabel verwijderen.

Dit kan je in een stored procedure doen; maar welk DBMS gebruik je ?

Ah ja, dat is wel een mogelijkheid.

Het geldt voor SQL 2000 (MSDE).

[Verwijderd]

Zoiets werkt ook en is waarschijnlijk efficienter:

code:

DELETE FROM tblTest 
WHERE TestId IN(
    SELECT   t1.TestId FROM tblTest t1
    INNER   JOIN tblTest t2 ON t1.Value = t2.Value
    WHERE   t1.TestId > t2.TestId
)

[robertpNL]

Verwijderd schreef op vrijdag 26 augustus 2005 @ 21:35:
Zoiets werkt ook en is waarschijnlijk efficienter:

code:

DELETE FROM tblTest 
WHERE TestId IN(
    SELECT   t1.TestId FROM tblTest t1
    INNER   JOIN tblTest t2 ON t1.Value = t2.Value
    WHERE   t1.TestId > t2.TestId
)

EEBB geweldig! Dit is wat ik zocht. Bedankt!

[Verwijderd]

Als je geen subqueries hebt (mysql):

select * from tabel

dan: indien de waarde al in je array voorkomt --> waarde verwijderen. Zo niet --> waarde aan array toevoegen.

[ACM]

Hoewel eigenlijk wat offtopic hezik, ik geef je geen gelijk:
Bij MySQL is het waarschijnlijk het snelst om een kopie van de tabelstructuur te maken (kan in 1 command) en dan de "goede records" erin selecteren. De oude tabel droppen en de nieuwe renamen.

Mocht het goede record wat complexer dan bovenstaand voorbeeld zijn, dan kan je eerst nog een temporary table maken met de query van whoami en die gebruiken om je originelen tegenaan te joinen.