How to update SQL Server column after from different table-CodePudding

I have a table called TableA that stored list of event like this

Event_Id     Event_Name
1            Found in AD
2            Found in AAD

I have another table call Table B and it look something like this

Event_Id    UserName      Extra
NULL        David         Found In AAD
1           James         Found in AD
Null        Ronal         Null

I'm just trying to update only a missing/Null value in TableB Event_ID column based on comparing Table1 Event_Name and TableB Extra columns.

I'm doing manually process like this for now so I would be really appreciated If I can get any help on how to join directly between the two table and update it.

Update Table B
  Set Event_Id = case
         when Extra = 'Found in AAD' then 2
 end

CodePudding user response：

You can use a simple sub-query in your UPDATE to do that.

UPDATE TableB SET
    Event_Id = (SELECT Event_Id from TableA a where a.Event_Name = TableB.Extra)
WHERE Event_Id is null;

CodePudding user response：

This will look for any matches on the Event_Name and Extra_Name and update the Extra table's Event_Id based on the match.

SQL:

UPDATE 
  Extra_Data 
SET 
  Extra_Data.Event_Id = a.Event_Id 
FROM 
  Event_Data a 
  INNER JOIN Extra_Data b ON a.Event_Name = b.Extra_Name 
WHERE 
  b.Event_Id IS NULL;

Results:

| Event_Id | UserName |   Extra_Name |
|----------|----------|--------------|
|        2 |    David | Found In AAD |
|        1 |    James |  Found in AD |
|   (null) |    Ronal |       (null) |

SQL Fiddle:

http://sqlfiddle.com/#!18/3a2ea/7

CodePudding user response：

update table_b 
set table_b.Event_Id = table_a.Event_Id 
from table_b
JOIN table_a  
ON table_b.extra  = table_a.Event_Name