-- DeleteByColorCode.sql
/*
2012-10-27 Tom Holden ve3meo

Deletes Persons and Families from PersonTable and FamilyTable respectively for 
persons with a specified color code. 

As written, the key color is RED (=1). Edit all instances of "= 1" to the desired code.
 0 = None,  
 1 = Red,  
 2 = Lime,  
 3 = Blue,  
 4 = Fuschia,  
 5 = Yellow,  
 6 = Aqua,  
 7 = Silver,  
 8 = Maroon,  
 9 = Green, 
10 = Navy, 
11 = Purple, 
12 = Brown, 
13 = Teal, 
14 = Gray

N.B.: This procedure results in many types of 'phantoms' and should be followed by
the procedure DeletePhantoms.sql
*/

-- Delete Family where one of the spouses has a colorcode match
DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable Where PersonID = FatherID) = 1;
DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable Where PersonID = MotherID) = 1;
-- Delete Person from PersonTable if matched on colorcode
DELETE FROM PersonTable WHERE Color = 1;