how to delete one occurence of duplicate records 2

[2969]

hi
i have a table which has thousands of duplicate records. i have this sql query that shows which records are duplicated and the number of times each record is duplicated.
select fielname, count(fieldname) from tablename havng count(fieldname) > 1

how can i keep one occurnce of the record and delete all the remaining duplicate of the record.
thx
TA

 

[jimstarr]

Hi TA,

SELECT * FROM tablename INTO CURSOR mycursor GROUP BY fieldname
SELECT tablename
ZAP
APPEND FROM DBF('mycursor')

Jim

 

[ramani]

Hi
What Jim said will work..
However, if the tables are not exclusively opened, ZAP will fail asking for exclusive mode.

So better alternative could be... to index on the (fieldname)... most likely you will have one... set order to that fildname..

SELECT myTable
SET ORDER TO myOrder
DO dupDelete
**************************************************
** procedure to delete duplicate records
** ussing current key field of the default alias ***
**
PROCEDURE dupDelete
LOCAL lcField, lcValue, lcAlias
lcAlias = ALIAS()
IF EMPTY(lcAlias)
RETURN
ENDIF
lcField = KEY()
LOCATE
lcValue = SPACE(LEN(&lcField))
SCAN
IF lcValue = &lcField
DELETE
LOOP
ENDIF
lcValue = &lcField
ENDSCAN
RETURN
***************************************************
Hope this helps you ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[CatalinC]

select fieldname,count(fieldname) as contor from yourdatabase having contor>1 into dbf tmpdatabase group by fieldname order by fieldname
*tmpdatabase will contain only records which have duplicate
*and a new field named contor which contain the number of *duplicates
close database
select 2
use yourdatabase
set order to yourorder
*if it is possible use an index for fieldname
select 1
use tmpdatabase
scan
sfieldname=fieldname
ncontor=contor
*the procedure will delete a number of contor-1 records
*for the records which have a number of contor duplicates
sele 2
*delete the first duplicate
locate for fieldname=sfieldname
delete
for i=1 to ncontor-2
continue
if not eof()
*delete next contor-2 duplicates
delete
endif
endfor
sele 1
endscan
close all

The procedure will verify and delete only records which have duplicates.

 

[dgrewe]

if you can get the file exclusive

set deleted off
delete all
index tag unique on (fieldname to make a unique fields) unique.
set order to unique
recall all
set order to

All duplicate records are no marked for deletion. Do what you want with them. David W. Grewe
Dave@internationalbid.com