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

Question

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:56 pm

#243204

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 Joe
12345a Sam
Data in Table A
A000045 12345a "J Smith" "12 anywhere st." "My Town, FL 33333"
I need to update the altname1 and altname2 fields in Table A with the atlname entries in Table b for each acctno where the data exists in Table B.
I need to update Table A in order to export all of this data to a csv file for use by an outside vendor
I have tried an update statement to update Table A
use Annual
update Table A
set TableA.altname1 = TableB.altname
from TableA, TableB
where TableA.acctno = TableB.acctno
I am at a loss as to how to update the altname2 field in TableA with the altname field TableB data that has not already been copied.
Thanks
terry

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Use a CTE and the row_number function to assign sequential numbers to each alternate name. Perform the update joining on the CTE.

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

if object_id('tempdb..#TableA') IS NOT NULL DROP TABLE #TableA;

if object_id('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;

CREATE TABLE #TableA (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,

altname2 nvarchar (50) NULL);

CREATE TABLE #TableB (acctno nvarchar (50) NULL,

altname nvarchar (50) NULL);

INSERT INTO #TableB

SELECT '12345a', 'Joe' UNION ALL

SELECT '12345a', 'Sam';

INSERT INTO #TableA (id_number, acctno, name, address1, address2)

SELECT 'A000045','12345a','J Smith','12 anywhere st.','My Town, FL 33333';

WITH CTE AS

(

-- assign a sequential number to each alternate number

SELECT acctno,

altname,

RN = ROW_NUMBER() OVER(PARTITION BY acctno ORDER BY altname)

FROM #TableB

)

UPDATE t1

SET altname1 = t2.altname,

altname2 = t3.altname

FROM #TableA t1

-- get the first altername number

LEFT JOIN CTE t2

ON t2.acctno = t1.acctno

AND t2.RN = 1

-- get the second altername number

LEFT JOIN CTE t3

ON t3.acctno = t1.acctno

AND t3.RN = 2;

-- show the results

SELECT *

FROM #TableA;

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

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

Wayne,

Thank you very much for the insight in how to perform this update. I apologize for not getting my original post setup right. The code worked perfectly.

Thank you 😀

terry