By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

UPDATE and SELECT Combination

P: 2
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.
Nov 9 '09 #1
Share this Question
Share on Google+
2 Replies


nbiswas
100+
P: 149
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

Expand|Select|Wrap|Line Numbers
  1. declare @tblA table(fieldA int)
  2.  
  3. declare @tblB table(fieldA int,fieldB int)
  4. insert into @tblA 
  5.     select 1 union all select 2 union all select 3 union all
  6.     select 4 union all select 5
  7.  
  8. insert into @tblB 
  9.     select 1,10 union all select 2,20 union all select 10,2 union all 
  10.     select 30,30 union all select 3,30
Solution 1:

Expand|Select|Wrap|Line Numbers
  1. update @tblA 
  2. set fieldA  =  X.TblBFieldB
  3. from 
  4. (select t2.fieldA TblBFieldA,t2.fieldB TblBFieldB from @tblA t1 
  5. inner join  @tblB t2
  6. on t1.fieldA = t2.fieldA) X(TblBFieldA,TblBFieldB)
  7. where fieldA = X.TblBFieldA  
  8. select * from @tblA 

Solution 2:

Expand|Select|Wrap|Line Numbers
  1. ;with cte as
  2. (select t2.fieldA TblBFieldA,t2.fieldB TblBFieldB from @tblA t1 
  3. inner join  @tblB t2
  4. on t1.fieldA = t2.fieldA
  5. )
  6. update @tblA 
  7. set fieldA  =  cte.TblBFieldB
  8. from cte
  9. where fieldA = cte.TblBFieldA  
  10. select * from @tblA 
Let me know in case of any concern
Nov 10 '09 #2

P: 2
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
Nov 10 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.