Query to Add a Column and Update values

Question

I have 3 table like the following. I want to add a column (City) to TableA and then update data in this column from TableC

TableA      
CustNumber  FirstName   LastName
A0001                       John              Doe
A0002                     Richard            Crena
A0004                       Elvis           Presley


TableB      
CustID  CustNumber  CustType
1              A0001                A
2              A0002                B
3              A0004                C


TableC      
CustNumber  CustAddress              CustCity
1                        9002 Sunset Blvd      Beverly Hills
2                       20403 Inglewood Ave    Inglewood
3                       1158 PC Hwy            Malibu

The end result should be

TableA      
    CustNumber  FirstName   LastName       CustCity
    A0001             John            Doe              Beverly Hills
    A0002           Richard          Crena             Inglewood
    A0004             Elvis         Presley            Malibu 

Answer 1

Hi @Malam Malam ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working:

--add column CustCity  
alter table TableA add  CustCity VARCHAR(100) NULL  
  
--update TableA  
update a  
set a.CustCity =c.CustCity  
from TableA a  
inner join TableB b on a.CustNumber=b.CustNumber  
inner join TableC c on b.CustID=c.CustNumber  
  
select * from TableA  

Output:

CustNumber FirstName LastName CustCity  
A0001 John Doe Beverly Hills  
A0002 Richard Crena Inglewood  
A0004 Elvis Presley Malibu  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.