-- Citations, Invisible - Convert to Personals.sql
/*
2012-09-10 by Tom Holden

Citations with CitationTable.OwnerType=7 that point to a Primary Name are invisible in 
the Edit Person and RM Explorer screens in version 5.0.4.1. They are also suppressed 
in Individual Summary and at least one Narrative Descendant report and probably others. 
Nonetheless, they are shown in the Source List report and survive Drag and Drop transfers. 

RM4 and 5 generate these citations on import of GEDCOM from GenBox and Ancestry.com 
and, possibly, from others. 

This query converts all such citations to Personal citatations, making them visible 
and useful again. It does so by changing the OwnerType to 0 and the OwnerID to the PersonID
that 'owns' the person's Primary Name. 
*/

UPDATE OR ROLLBACK CitationTable
SET OwnerID = 
(
 SELECT NameTable.OwnerID 
 FROM CitationTable AS Old
 LEFT JOIN NameTable
 ON Old.OwnerID = NameTable.NameID
 WHERE Old.CitationID = CitationTable.CitationID
),
OwnerType = 0
WHERE CitationID IN
(
 SELECT CitationID
 FROM CitationTable
 LEFT JOIN NameTable
 ON CitationTable.OwnerID = NameTable.NameID
 WHERE CitationTable.OwnerType = 7 AND +NameTable.IsPrimary
)
;