kyan kyan - 12 days ago 6
SQL Question

UPDATE using SQL Server

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

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
Comments