Sorry for the delay in an answer. I was a bit busy the past couple of days.
Below is my answer based on the following assumptions:
1) You will be cleaning up name spelling issues via some other mechanism (you mentioned that you would clean it up using regular expressions in a comment to your original question).
2) A DUP set can be identified using Firstname, Lastname, and Birth (you mentioned this in a comment to your original question).
3) Firstname, Lastname, and Birth can not be NULL.
4) You cannot have more than one FIN record in a DUP set (you mentioned this in a comment to your original question).
If any of the above assumptions are not valid then my answer will have to be modified.
The following are the steps to take:
1) Update all FIN records to copy over the PersonalKey from the non-FIN record:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source like 'FIN%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
2) Delete all non-FIN records where we have a FIN record:
DELETE p2
FROM Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
WHERE p1.Source like 'FIN%'
AND p2.Source not like 'FIN%';
At this point all the DUPs with a FIN record have been cleaned so that only the FIN record is left.
3) If we decide to stay with the DRV record for all other cases. We will need to copy the PersonalKey from another record to the DRV record:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source like 'DRV%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
4) Delete all non-DRV records where we have a DRV record:
DELETE p2
FROM Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
WHERE p1.Source like 'DRV%'
AND p2.Source not like 'DRV%';
At this point all the DUPs with a DRV record have been cleaned so that only the DRV record is left.
If the only other record type is the SA record, then there should be no more DUPs left and we're done.
5) If we want to choose the record with the most information filled in or if we finished 3 and 4 and there is more than one record type still left causing DUPs. We need to copy the PersonalKey from any record in a DUP set that has it to any record that doesn't have it for all non-FIN records:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source not like 'FIN%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
6) Delete all records except for the one with the most information (as defined by the info_score computed column):
DELETE p5
FROM Pruebas p5
INNER JOIN (SELECT p3.Firstname
, p3.Lastname
, p3.Birth
, MIN(p3.Id) AS min_id
FROM Pruebas p3
INNER JOIN (SELECT p1.Firstname
, p1.Lastname
, p1.Birth
, count(*) AS c
, MAX((p1.Name is not null) + (p1.RFC is not null) + (p1.Source is not null) + (p1.PersonalKey is not null)) AS info_score
FROM Pruebas p1
GROUP BY p1.Firstname
, p1.Lastname
, p1.Birth
HAVING count(*) > 1) p2
ON p3.Firstname = p2.Firstname
AND p3.Lastname = p2.Lastname
AND p3.Birth = p2.Birth
AND ((p3.Name is not null) + (p3.RFC is not null) + (p3.Source is not null) + (p3.PersonalKey is not null)) = p2.info_score
GROUP BY p3.Firstname
, p3.Lastname
, p3.Birth) p4
ON p4.Firstname = p5.Firstname
AND p4.Lastname = p5.Lastname
AND p4.Birth = p5.Birth
AND p4.min_id <> p5.Id;
At this point all the DUPs have been collapsed with the PersonalKey saved if it was available and FIN records were saved if they existed otherwise either the DRV record or the record with the most information was saved.
Let me know if you have any questions with any of the above.
Hope it helps,
-Dipin