Solved

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

Posted on 2008-10-27
4
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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