In SQL, how do you update each row of a table, selecting all rows that are equal for a column, then set another column equal to eachother

Question

So basically it will be psuedo code, but I don’t know how to do it in SQL, please help.

for each row in table1{ loop through each row in table 2 { if table1 row.column 1 = table2 row.column 2 for this row { set table1 row.col2 = table2 row.col2 } } } 

Edit: Ok, let me be more specific. We basically switch from the hibernation sequence as id to using the guidance for the id column. I am trying to update foreign keys associated with creating a temporary column of the previous foreign key column, and then matching temporary columns to update the actual columns.

Suppose that in table one there was an identifier, and in table two there was a column for these identifiers to be used as foreign keys. I want to use the previous values ​​in table 2 to match the rows in table 1 and set the key values ​​in table 2 to match the new pointer to table 1.

therefore, table 2 may contain multiple rows of duplicate id, but in table 1 there will never be duplicates. If that makes sense.

Answer 1

In SQL Server, you can do something like:

  UPDATE Table_1
 SET Column_2 = t2.Column_2
 FROM Table_1 AS t1
 INNER JOIN Table_2 AS t2 ON t2.Column_1 = t1.Column_1

or something like

  UPDATE Table_1
 SET Column_2 = ( 
     SELECT t2.Column_2
     FROM Table_2 AS t2
     WHERE t2.Column_1 = Table_1.Column_1
 )

Of course, if you have multiple rows in table_2, you will get an error message ....

Answer 2

The basics of this:

 UPDATE Table1 SET col2 = (select col2 FROM Table2 where Table2.column2 = Table1.column1) 

But this may not do exactly what you need if there is no 1-1 correspondence between the rows in the two tables, and therefore my current comment is below your question:

What happens if there are several matching rows in table 2? What should happen if there is no corresponding line?