update table with multiple values from another table - all data to be on same row

Question

Post reply

update table with multiple values from another table - all data to be on same row

terry.davis

SSC Rookie

Points: 25
More actions
November 23, 2010 at 7:42 pm

#241584

I have a database with several tables in it. I need to update one table (Table A) with data from Table B.
Table A structure
id_number nvarchar 50 NULL
acctno nvarchar 50 NULL
name nvarchar 50 NULL
address1 nvarchar 50 NULL
address2 nvarchar 50 NULL
altname1 nvarchar 50 NULL
allname2 nvarchar 50 NULL
Table B structure
acctno nvarchar 50 NULL
altname nvarchar 50 NULL
Data in Table B
12345a Joe1
12345a

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

terry.davis SSC Rookie Points: 25 More actions · Answer 1

please disregard this post as i inadvertently hit submit.

terry

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Update TableA.name column with values from TableB.altname ?

UPDATE A

SET A.name = B.altname

FROM TableA A

JOIN TableB B

ON A.acctno = B.acctno

Wont this do for you?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

terry.davis (11/23/2010)
please disregard this post as i inadvertently hit submit.
terry

:-D:-P

terry.davis SSC Rookie Points: 25 More actions · Answer 4

thanks for the reply but I need to update the altname1 and altname2 fields, not the name field, with the two entries in TableB for the acctno.

thanks

terry

WayneS SSC Guru Points: 95461 More actions · Answer 5

Duplicate post - please post all replies to this thread. A solution has already been provided over there.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2