-- SpouseID_Invalid-Reset.sql
/* 2017-02-20 Tom Holden ve3meo
rev 2017-02-21 corrected misinterpretation of PersonTable.SpouseID; it is actually the value of the FamilyID

Resets to 0 the invalid SpouseIDs in PersonTable.
which can give rise to strange behaviour such as empty Family View.
*/
DROP VIEW IF EXISTS vHasValidSpouseID
;

CREATE TEMP VIEW vHasValidSpouseID AS 
SELECT P.PersonID, F.FamilyID 
FROM PersonTable P
JOIN FamilyTable F
WHERE 
    (P.PersonID = F.FatherID
     AND P.SpouseID = F.FamilyID
     )
 OR (P.PersonID = F.MotherID
     AND P.SpouseID = F.FamilyID
     )
;

UPDATE PersonTable
SET SpouseID = 0
WHERE PersonID NOT IN
(SELECT PersonID FROM vHasValidSpouseID)
AND SpouseID <> 0
;