[FIXED] How to update each row of a column from one table with a list of values from another?

Issue

I have a table A with records with 1 column only that are random alphanumerical characters. That table has say 10 rows.

I have another table B with 10 rows also that I want to take a value from a row from table A and apply it to a row in table B.

So basically, take a value from Table A and assign it to a row in table B. Preferably, take the value from table A row 1 and assign it to table B row 1, etc...

I am using SQL Server.

We can take any value from table B to assign to a row in table A. We just can't re-use a value from table B.

Here are the 2 tables in it's simplest form for this example:

CREATE TableA ([Value] NVARCHAR(50))

CREATE TableB ([Value] NVARCHAR(50))

Solution

Given the following table structure:

CREATE TABLE #tempA (stringEntry NVARCHAR(50));
INSERT INTO #tempA (stringEntry) VALUES ('abcd'), ('efgh'), ('ijkl');

CREATE TABLE #tempB (stringEntry NVARCHAR(50));
INSERT INTO #tempB (stringEntry) VALUES ('mnop'), ('qrst'), ('uvwx');

You can do the following:

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempA.stringEntry) AS RowNumber,
  #tempA.stringEntry AS entryA
INTO #tempA2
FROM #tempA;

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempB.stringEntry) AS RowNumber,
  #tempB.stringEntry AS entryB
INTO #tempB2
FROM #tempB;

UPDATE #tempA 
SET #tempA.stringEntry = #tempB2.entryB
FROM #tempA
INNER JOIN #tempA2 ON #tempA.stringEntry = #tempA2.entryA
INNER JOIN #tempB2 ON #tempB2.RowNumber = #tempA2.RowNumber;

This assumes that you have equal number of rows in each table, as you indicated, or are okay with having the "excess" entries in your first table not being updated.

Answered By - mflournoy

Answer Checked By - Clifford M. (PHPFixing Volunteer)