Solved

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

Posted on 2008-10-27
4
579 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now