So i have a table looking like this:
id name dad mom color
1 bob 12 4 NULL
4 embla NULL NULL blue
12 clyde NULL NULL blue
2 anna 12 4 NULL
I would like to SET anna & bobs color based on their parents color. It might not alway be blue..
Im not very good at this but ive tried to alter another code i used for something else but that didnt work :P
UPDATE table1 AS p
INNER JOIN table1 AS Dad
ON p.dad_id = dad.id AND p.mom_id = mom.id
IF color = blue
SET p.color = blue
this is how far i get and where i struggle... as i dont know how to check for the parents color and then return the same color to the offspring...
So i want to set the same color as their parents in the color column, and if the parents color do not match i would like it to be NULL.
I dont have many colors to choose from so i can use the value "blue" i the code. i dont need to actually "match" the color value, i can use the value blue, red or yellow and run the script 3 times.