UPDATE and SELECT Combination

[rickcf]

I have two tables. Table A is the main table I need to update and table B is the update table with the new data. Table B contains only two fields- fielda (also contained in table A for the linking but also is the field that needs changed) and a second field (fieldb) which is the NEW value of fielda that I need to change in TableA. Thus I need a combination UPDATE/SELECT statement to link the two tables togeather to update table A with the new values (fieldb) from table B. I need a start on how I can do this. Thanks.

[nbiswas]

This may help.. I am providing 2 solutions based on my understanding of your question

Declare the two tables and insert some values to them

declare @tblA table(fieldA int)
 
declare @tblB table(fieldA int,fieldB int)
insert into @tblA 
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5
 
insert into @tblB 
    select 1,10 union all select 2,20 union all select 10,2 union all 
    select 30,30 union all select 3,30

Solution 1:

update @tblA 
set fieldA  =  X.TblBFieldB
from 
(select t2.fieldA TblBFieldA,t2.fieldB TblBFieldB from @tblA t1 
inner join  @tblB t2
on t1.fieldA = t2.fieldA) X(TblBFieldA,TblBFieldB)
where fieldA = X.TblBFieldA  
select * from @tblA 

Solution 2:

;with cte as
(select t2.fieldA TblBFieldA,t2.fieldB TblBFieldB from @tblA t1 
inner join  @tblB t2
on t1.fieldA = t2.fieldA
)
update @tblA 
set fieldA  =  cte.TblBFieldB
from cte
where fieldA = cte.TblBFieldA  
select * from @tblA 

Let me know in case of any concern

[rickcf]

Perfect! Just the start I needed! Here is the working code I got to work in case anyone else should need it:


update tablea
set tablea.fielda = X.newfield from
(select t2.oldfield, t2.newfield from tablea t1
inner join tableb t2
on t1.fielda = t2.oldfield) X
where tablea.fielda = X.oldfield


Thanks!

Rick