>>>Now I want to to somehow de-dup these patients. First of all, what would be a logical assumption to de-dup? Leave the Patients, that have more transaction records associated with them? Or leave the patients with the newest cEntered_Date? Say, I want to use the second criterion. How can I identify these Patients? May be I can change my original query to include cPatients_pk? But how would I use the latest cEntered_Date?
>
>
>>>
>>>Once I select the master patients_pk, I still need all other patients PK, so I can re-point transaction records to the master PK. I can delete records in Address, Names, Phones, that point to other PKs after I re-point Transaction records.
>>>
>>>Could you please suggest some ideas? Should I try to scan this curDupPatients and try to populate cPatients_pk cursor?
>>>
>>>Thanks in advance.
>>___________________________________
>>Hi Nadya,
>>
>>The most logical to me is to de-dup on Social Security Number (and I assume that no two persons can have the same)
>>
>>Once you have identified those you can alter the ParentKey of all levels just below your patient table (say this is level 2). If the PatientPk is on levels 3 or more, a trigger with cascaded update may help
>>
>>The obsolete Patients could be deleted then
>>
>>Just a thought
>>
>>
>>*** make a good backup first ***

__________
>No, it's not enough to de-dup on SSN + dDOB. We found cases there different patients have the same SSN & dDOB (perhaps, Identity theft cases, but it's not our job to report them). That's why I want to use a complex query. Also we may have situation when we changed address or added phone for patients. I want to preserve these records, even if it means we would not have them all properly de-duped. So, I want to save as much info as possible and de-dup based on the complex criteria rather than just SSN and DOB.
___________

OK, I surrender