I have a database of race participants. In the past every entry was seen as a unique entity even though many were already in the database from a race they ran earlier. Further, every participant is associated with several other tables (not all of which are FK-PK related). I want to identify the duplicates, change the unique id in the related tables, then delete the participant from the participant table).
I would to do this via my classic asp portal so that I can re-use the utility in the future (although I am re-writing my code to check for existence when entering participants).
What's the best way to do this? Here is my thought:
1) Do it one letter at a time (last name).
2) Order by gender, last name, first name.
3) Write existing participants to an array sorted as above.
4) Cycle through the array looking for matches (I can select the fields to look for matches on and I can see the list of participants).
5) When a match is found call a function that changes the participant id on the related tables.
6) Delete the duplicate entry from the participant table.
I will also write the utility to compare one-at-a-time and condense manually. I just want a way to make a couple of passes taking care of the obvious ones.
What am I missing? Is there an easier way?