>>>>Hi All:
>>>>What's the easiest and most elegant way to remove the SECOND duplicate.
>>>>IOW, when there is a dupe, get rid of the second record.
>>>>Thanks,
>>>>Yossi
>>>
>>>
>>>There may be a better way. I have used this.
>>>
>>>
>>>SELECT tableWithDuplicates
>>>ALTER TABLE ADD COLUMN lDuplicate L
>>>
>>>* Mark everything initially as a duplicate
>>>REPLACE ALL lDuplicate WITH .T.
>>>
>>>* Create an index of unique records
>>>INDEX ON my+Unique+Key+Fields UNIQUE TAG dupTemp
>>>
>>>* Mark every unique record as not being a duplicate (the nature of the INDEX ON command mandates that the UNIQUE records are top-most in row order)
>>>REPLACE ALL lDuplicate WITH .F.
>>>
>>>* Remove the index
>>>SET INDEX TO
>>>
>>>* The records which are marked lDuplicate = .T. are the second (and later) duplicates
>>>
>>>* Delete those records and then PACK, mark lDeleted = .T., or whatever...
>>>
>>>ALTER TABLE DROP COLUMN lDuplicate
>>>DELETE TAG dupTemp
>>>
>>Sorry Rick,
>>I am afraid this will not work since Yossi replied the key's are same therefore not unique.
>>Yossi wants to delete the second record, whatever that means, containing corrupt data.
>>Better to make a SQL select on corrupt data conditon, however Yossi has not replied what qualifies his data as begin corrupt.
>>Rgds,
>>Koen
>
>
>It will work. The key fields I mention are not table keys, but uniquely identifying index keys.
Rick,
and how does your code know if the record is corrupt? Who says that the second record your code found is the second record? Unless we are dealing here with a situation e.g. like:
table is filled with n correct reccords from a source (table, Excel sheet or what ever)
appended again with n reccords from a different but wrong source and therefore qualified as 'corrupt'
therefore the 2nd, wrong, update should be deleted.
This all is assumptions. Yossi should indicate / confirm.
Let us wait.
Rgds,
Koen
.