Milla Milla - 1 year ago 39
SQL Question

update cell value based on result from 2 other cells value

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 = AND p.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.

Answer Source
update table1 p join
table1 dad
on = p.dad_id and dad.color = 'blue' join
table1 mom
on = p.mom_id and mom.color = 'blue'
set p.color = 'blue';