Update table column based on another table-CodePudding

I have a table Table1 that look something like this:

Number     Name    Exist
-------------------------
245435     John
64443      Sam
57133      *NULL*
89373      Jame

and another table Table2:

Number     Name    
----------------
245435     John
64443      Sam
*NULL*     Jame

I'm just wondering how to update the Table1's Exist column with "YES" OR "NO", depending on whether either Table1.Number or Table1.Name or both also exist in Table2. So one value need to be found in Table2.

I have this so far but not sure how to add YES or NO.

UPDATE table1 
SET Exist = (SELECT TOP 1 Number 
             FROM table2 
             WHERE table1.Number = table2.Number 
                OR table1.Name = table2.Name);

CodePudding user response：

You just need to add a CASE expression

UPDATE table1 
SET Exist = (CASE 
            WHEN EXISTS (SELECT 1 
                         FROM table2 
                         WHERE table1.Number = table2.Number 
                            OR table1.Name = table2.Name) 
            THEN 'YES' 
            ELSE 'NO' 
         END);