"Lucas Franzen" <luc@...> wrote in message
news:3C00074C.9EFEBC5A@......

>
>
> Depending on the size of this table it might be faster to write a stored
> proc for doing so, like:
>
> create procedure sp_del_duplicates
> as
> declare variable dupl integer; /* or whatever your field is */
> begin
> for select yourfield
> from yourtable
> group by yourfield
> having count (*) > 1
> into :dupl
> do begin
> delete from yourtable where yourfield = :dupl;
> end
> end

Lucas, you seem to be deleting every occurrence of a duplicate. Wasn't the
idea to reduce N occurrences of a value to only one occurrence? I think what
you need is:

create procedure sp_del_duplicates
as
declare variable dupl int; /* or whatever your field is */
declare variable loop int;
declare variable dbk char(8);
begin
for select yourfield, count(*)
from yourtable
group by yourfield
having count (*) > 1
into :dupl, :loop
do begin
for select t.rdb$db_key from yourtable t
where t.yourfield = :dupl
into :dbk
do begin
count = count - 1;
if (count > 0)
then delete from yourtable t where t.rdb$db_key = :dbk;
end
end
end

When there are duplicates, you cannot put the WHERE on the same field or you
wipe out ANY occurrence instead of leaving one in place. The only way to
identify those records is using the raw record position:
http://www.cvalde.com/document/mysteriousDbKey.htm

Even more, page 4 of that topic shows a 6-lines instruction that cleans a
table from any number of duplicates, leaving only one value in place for
each repetition.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing