question

MalamMalam-4042 avatar image
0 Votes"
MalamMalam-4042 asked EchoLiu-msft edited

Query to Add a Column and Update values

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 


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MalamMalam-4042 commented

Hi @MalamMalam-4042,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks!
It worked great based on the question I posted but I just realized and noticed an issues.

TableC has multiple records for each CustNumber and I want to pull ONLY the first record for that CustNumber and update in CustCityTableA

I will mark your answer as "Accept Answer". Please let me know if the same query would work for the scenario I've just mentioned.

0 Votes 0 ·