AdPay-Systech schrieb:

>
> I have a database that is suppose to have unique records in it, however when I try to create a unique index I get an error saying thier are duplicate values. Is there a simple way to identify and delete the duplicates.

Ron,

For getting the duplicates you can use:

SELECT FIELD_WHICH_SHOULD_BE_UNIQUE, COUNT (*)
FROM TABLE_THAT_SHOULDNT_HAVENT_DUPLICATES
GROUP BY FIELD_WHICH_SHOULD_BE_UNIQUE
HAVING COUNT (*) > 1

This will give you a list of all fields that are in the table more than
once.

note: you don't need the count(*) in the select clause, it's just if
you're interested how mayne times each duplicate value is in the table.


If you don't want to delete them manually you can use this query as the
input for a delete (like delete from table where exists ( select ....
)), but this won't be very fast I think.

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

That's all.

HTH
Luc.