/*
spouse_color_to_green.sql  Jerry Bryan 2024=04-21

Set any people whose color is 0 (namely black) to green
if their spouse has a color of red.

*/

UPDATE PersonTable
SET Color4 = 9    -- green for spouses
WHERE PersonID IN
(
SELECT F.MotherID AS SpouseID
FROM PersonTable AS P
JOIN FamilyTable AS F ON P.Color4 = 1 AND P.PersonID = F.FatherID

UNION

SELECT F.FatherID AS SpouseID
FROM PersonTable AS P
JOIN FamilyTable AS F ON P.Color4 = 1 AND P.PersonID = F.MotherID
)
AND Color4 = 0