我的SP中有临时表,数据库中有一个表,我需要更新数据库中的表,到目前为止,我可以使用select语句更新表。
但是我的临时表中有多个记录,我只能在数据库中更新表的最后一行。
以下是我的查询,
UPDATE
Table_A
SET
Table_A.col2 = Table_B.col2,
Table_A.col3 = Table_B.col3
FROM
Some_Table AS Table_A
INNER JOIN temp_Table AS Table_B ON Table_A.col1 = Table_B.col1
和数据库表结构
col1 | col2 | col3
1 | India | Delhi
2 | US | NewYork
3 | UK | London
和临时表结构如下
col1 | col2 | col3
1 | US | NewYork
2 | UK | London
3 | India | Delhi
因此,我需要更新多行的表。
据我了解您的帖子,我认为这应该是正确更新的解决方案。希望这有帮助
WITH CTE1
AS ( SELECT Col1 ,
Col2 ,
ROW_NUMBER() OVER ( PARTITION BY COl1 ORDER BY Col2 ) AS rn
FROM table_1 t1
),
CTE2
AS ( SELECT Col1 ,
Col2 ,
ROW_NUMBER() OVER ( PARTITION BY COl1 ORDER BY Col2 ) AS rn
FROM table_2 t2
)
UPDATE br
SET ....
FROM Cte1 c1
INNER JOIN cte2 c2 br ON c1.Col1 = c2.Col1
AND c1.rn = c2.rn;