How to update each row of a column from one table with a list of values from another?-CodePudding

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))

CodePudding user response：

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.