Link to home
Start Free TrialLog in
Avatar of logoncom
logoncom

asked on

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?

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of J_Carter
J_Carter

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

sry Angelll your to fast for me :-(
Avatar of logoncom

ASKER

Great... the saves me!!