Milla Milla - 5 months ago 6
SQL Question

update cell value based on result from 2 other cells value

So i have a table looking like this:

table1

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.

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