• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

updating multiple rows in one sql 2005 database table using data from a differerent table

Im trying to update a table with values by writing one query.  I have a table that is missing name, address, phone, city, state and I have another table that has this missing information.  Each table share a common field called customerno.

How can I write a query so all the data in the table that has the information will update the table that does not have the information... without having to go one by one.

I'm getting my data with this:  
select  ccustno, rtrim(cfname) + ' ' + rtrim(clname), caddr1, caddr2,ccity,cstate,czip,cphone1,cfax from dbo.arcust_10_27_08 where ccustno = @ccustno

Then I want to update my other table with this:

update sales_temp
set ccontact = @contact
, cphone = @phone
, cfax = @fax
,caddress = @address
, caddress2 = @address2
, ccity = @city
, cstate = @state
, czip = @zip
where ccustno = @ccustno

How can I combine the 2 queries to update 700 records at once?

0
logoncom
Asked:
logoncom
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
update st
set ccontact = rtrim(a.cfname) + ' ' + rtrim(a.clname)
, cphone = a.cphone1
, cfax = a.cfax
,caddress = a.caddr1
, caddress2 = a.caddr2
, ccity = c.ccity
, cstate = a.cstate
, czip = a.czip
from sales_temp st
join dbo.arcust_10_27_08 a
  on a.ccustno = st.ccustno

Open in new window

0
 
J_CarterCommented:
here you go.
update sales_temp
set ccontact = A.contact
, cphone = A.phone
, cfax = A.fax
,caddress = A.address
, caddress2 = A.address2
, ccity = A.city
, cstate = A.state
, czip = A.zip
from sales_temp ST
join dbo.arcust_10_27_08 A on ST.ccustno = A.ccustno

Open in new window

0
 
J_CarterCommented:
sry Angelll your to fast for me :-(
0
 
logoncomAuthor Commented:
Great... the saves me!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now