Solved

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

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL to Update Table Dynamically 2 57
Updating statistics with error notification email in SQL server 4 110
SQL Restore Script - Syntax Error 8 104
Restrict result set 1 39
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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