-2

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 ||

Special Circumstances Agent
  • 197
  • 2
  • 10
  • This might help https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query Instead of `INNER JOIN` it would be `LEFT JOIN` and then you would check for null/empty values. – Reinis Verbelis Jan 29 '22 at 17:17
  • *Here's the code* This code is obviously **syntactically errorneous** in MySQL. Please check your DBMS carefully and adjust the question tag accordingly! – Akina Jan 29 '22 at 17:45

2 Answers2

1

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;
forpas
  • 160,666
  • 10
  • 38
  • 76
0

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
jsaigle
  • 444
  • 2
  • 7