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

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

*/
UPDATE PersonTable
SET Color4 = 3    -- blue for parents of spouses
WHERE PersonID IN
(
SELECT F.FatherID AS ParentID
FROM ChildTable AS C
JOIN PersonTable AS P ON P.PersonID = C.ChildID AND P.Color4 = 9
JOIN FamilyTable AS F ON F.FamilyID = C.FamilyID

UNION

SELECT F.MotherID AS ParentID
FROM ChildTable AS C
JOIN PersonTable AS P ON P.PersonID = C.ChildID AND P.Color4 = 9
JOIN FamilyTable AS F ON F.FamilyID = C.FamilyID
)
AND COLOR4 = 0
