Still the same idea ;))

Update MyTable
set flag='D'
where MyTable.IDField IN
(Select MT.IDField
from MyTable MT
where MT.MyDateField betwen m.ldDate - 10 AND m.ldDate + 10
group by (ccode+town+street+str(strnum,4)+...), MT.IDField
having count(MT.IDField)>1)

I hope this will work, I'm not sure if we can use exression in GROUP BY list.


>>Hi!
>>
>>The same idea. 'Where' will filter out records for count() function.
>>
>>Select IDField, count(IDField) as nCount
>> from MyTable
>> where MyDateField betwen m.ldDate - 10 AND m.ldDate + 10
>> group by IDField
>> having nCount>1
>
>Hi Vlad,
>
>Actually, I need to mark all duplicate records in myTable (set flag='D'), which have the same address (ccode+town+street+str(strnum,4)+...) and date within the range. How can I do this?
>
>My current solution is quite combersome, so I'm open for the suggestions.
>
>Thanks in advance.
>>
>>>>>I'm switching an index to a primary and am getting a duplicate index message. How can I find the duplicates amoung 1000's without
>>>>>looking through them all?
>>>>
>>>>Select IDField, count(IDField) as nCount
>>>> from MyTable
>>>> group by IDField
>>>> having nCount>1
>>>
>>>This question was asked so often, what it definitely should be in a FAQ.
>>>
>>>BTW, a little more complicated variation of this problem:
>>>Find all duplicates, which have the same Id field and date within date specified +-10.