>Hello:
>I'm trying to write a program to remove duplicate records from my
>database table. I need to remove the index first, scan for duplicates, delete all and then recall the first record of each duplicate and then add the regular index back. CAn I do this
>without having to resetting the relationships up in the database?
>
>THis is my rough code. Any help would be appreciated.
>
>close all
>set defa to \wincats\data
>
>use act_plan
>delete tag sincase of act_plan
>
>
>set order to sin
>go top
>dsin = sin
>dadddate = add_date
>daddtime = add_time
>
>Scan
> locate for sin = dsin, add_date = dadddate, add_time = daddtime
> Do while found()
> delete
> go top
> recall
> pack
>Endscan
>
>ALTER TABLE Act_plan ADD PRIMARY KEY Sin + Caseno TAG Sincase
You may keep it simpler. For example, you have a table where you want to remove duplicates by fields: field1+field2 (both characters to make it simpler), and you have tag 'fields' on field1+field2. Here the code:
Select field1,field2,count(*) as counter ;
From mytable ;
Into Cursor tmp ;
Group by 1,2 ;
Having Count(*)>1
Select mytable
Set Order to Tag Fields
Select tmp
Scan
IF Seek(tmp.field1+tmp.field2,"mytable")=.t.
Skip In Mytable
nDeleterecords=tmp.counter-1
Delete Next nDeleterecords In Mytable
Endif
Endscan
I think you may backup your data before trying this.
Edward Pikman
Independent Consultant