Merged and Deleted Penn IDs

In order to find the Penn ID into which a duplicate record has been merged, query the view COMADMIN.CHANGED_PENN_ID:

Merged Penn IDs

The following query will return all rows for merges that have not been reversed:

select v_subsuming_penn_id, v_merged_date, v_merged_comments
from COMADMIN.CHANGED_PENN_ID
where v_reversed_date is null
order by v.subsuming_penn_id

Sometimes, more than one duplicate record has been merged. A query searching for possible multiple merged Penn IDs requires the view to be joined to itself, as in this query:

select a.v_subsuming_penn_id as "Current and Surviving Penn ID",
a.v_subsumed_penn_id as "Merged Penn ID",
b.v_subsuming_penn_id as "Prior Surviving Penn ID",
b.v_subsumed_penn_id as "Prior Merged Penn ID"
from COMADMIN.CHANGED_PENN_ID a,
COMADMIN.CHANGED_PENN_ID b
where a.v_subsumed_penn_id = b.v_subsuming_penn_id
and a.v_reversed_date is null
and b.v_reversed_date is null
and a.v_subsuming_penn_id <> a.v_subsumed_penn_id

We see in the query results:

The surviving PennID is 99999999, into which 8888888 was merged, into which 77777777 was merged.

Any script searching for merged PennIDs should loop through the condition subsumed_penn_id = a subsuming_penn_id until subsumed_penn_id != a subsuming_penn_id.

Deleted Penn IDs

Sometimes a duplicate ID will be identified before any significant data has been entered on the record and can be deleted immediately. In this case, the view will be populated as follows:

SELECT *
FROM COMADMIN.CHANGED_PENN_ID
WHERE V_SUBSUMING_PENN_ID = V_SUBSUMED_PENN_ID
AND V_REVERSED_DATE IS NULL

Any questions about merged Penn IDs should be directed to penncommunity@isc.upenn.edu,

Contact: penncommunity@isc.upenn.edu