UPDATE using SQL Server

Question

I have one table, TABLE1, which can be presented by:

select * from TABLE1 order by id_conv

Such as:

id_conv
=======
200
201
202

Another table TABLE2 which can be presented by:

select * from TABLE2 order by id

Such as:

id | id_conv
============
 1 | NULL
 2 | NULL
 3 | NULL

Now I want to update the table TABLE2 with the values of TABLE1.

1st row value in TABLE1 to 1st row in TABLE2
2nd row value in TABLE1 to 2nd row in TABLE2
3rd row value in TABLE1 to 3rd row in TABLE3

like:

id | id_conv
============
 1 | 200
 2 | 201
 3 | 202

How can I do it?

Answer 1

Using ROW_NUMBER() function, you can assign numbers based on desired ordering for both tables and join on it for update

WITH CTE_Table1 AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id_conv) RN --set desired ordering
    FROM Table1
)
,CTE_Table2 AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) RN --set desired ordering
    FROM Table2
)
UPDATE t2
SET t2.id_conv = t1.id_conv
FROM CTE_Table1 t1
INNER JOIN CTE_Table2 t2 on t1.RN = t2.RN