Hi!
>Nope, this would not work. Do you want me to explain why or can figure it by yourself? :)
Well, expression should be (MT.ccode+MT.town+MT.street+str(MT.strnum,4)+...), right? I did not tried such king of queries, so will be glad to know exactly what is wrong ;)
>
>>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.
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.