How do I update SQL query Yes/No?-CodePudding

I'm need to update sql table using if condition comparing another table. For example if phone numbers from 'a' table matches with 'b' table. The new column 'flag' is updated to Y/N.

Here's the code, it's working fine for 'Y' but how do I update the NULL or empty strings with 'N'

Here's the code:

update table_1 as a

set flag = 'Y'

from table_2 as b

where a.phone_numbers = b.phone_numbers

It is successfully working for 'Y' but how do I implement for 'N'

Thank you for your time ||

CodePudding user response：

Use a LEFT join of table_1 to table_2:

UPDATE table_1 t1
LEFT JOIN table_2 t2 ON t2.phone_numbers = t1.phone_numbers
SET t1.flag = CASE WHEN t2.phone_numbers IS NOT NULL THEN 'Y' ELSE 'N' END;

CodePudding user response：

I think this could work for you. All I've done is replace 'Y' with 'N' and change the = symbol to !=, which means 'is not equal to'.

Using these two queries you should get the results you want.

update table_1 as a

set flag = 'N'

from table_2 as b

where a.phone_numbers != b.phone_numbers