Solved

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

Posted on 2008-10-27
4
585 Views
Last Modified: 2010-04-21
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
Comment
Question by:logoncom
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22817341
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
 
LVL 6

Expert Comment

by:J_Carter
ID: 22817352
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
 
LVL 6

Expert Comment

by:J_Carter
ID: 22817357
sry Angelll your to fast for me :-(
0
 

Author Closing Comment

by:logoncom
ID: 31510541
Great... the saves me!!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question