如何从SQL Server中的两个表更新一对一行 [英] How to update one-one row from Two table in Sql Server

问题描述

亲爱的开发人员，

我有两个表"Tbl_ATemp"和"Tbl_BTemp"
我想从第二个表Tbl_BTemp的ID更新第一个表列"B_Id".
tran_id与表相同，ID列为标识.
我会尝试使用游标，但没有得到想要的结果

我的桌子是....

Tbl_ATemp

Id Tran_Id B_Id
101444空
102444空
103444空
104444空

Tbl_BTemp

Id Tran_Id A_Id
1444 NULL
2444 NULL
3444 NULL

在更新表Tbl_Atemp之后，我想将结果显示为

Tbl_ATemp

Id Tran_Id B_Id
101 444 1
102 444 2
103 444 3
104444空

它的非关闭一个-来自表和更新列的一条记录.
如果有提供采石场或解决采石场的任何方法，对我来说非常有用.

问候，
Ravi Sharma

Dear Developer,

I have the two table "Tbl_ATemp" And "Tbl_BTemp"
I want to update first Table coloumn "B_Id" from ID of second table Tbl_BTemp.
The tran_id is same both table and ID coloumn is identity.
I will try cursor but not got result that i want

My table is....

Tbl_ATemp

Id Tran_Id B_Id
101 444 NULL
102 444 NULL
103 444 NULL
104 444 NULL

Tbl_BTemp

Id Tran_Id A_Id
1 444 NULL
2 444 NULL
3 444 NULL

After the Updating Table Tbl_Atemp I want to show Result as

Tbl_ATemp

Id Tran_Id B_Id
101 444 1
102 444 2
103 444 3
104 444 NULL

Its noncoff one - one record from both table and update coloumn.
Its greatful for me if any provide me quarry or any method to solved my quarry.

Regards,
Ravi Sharma

推荐答案

尽管这不是一般情况，但我已经设计了一种方法.希望能解决您的问题.

Though it should not be a general scenario, I have devised a way. Hope it will solve your problem.

Select AId,BId
INTO #tmp
from
   (select Id as AId, ROW_NUMBER() over (order by Id) r from Tbl_ATemp) a
    join
   (select Id as BId, ROW_NUMBER() over (order by Id) r from Tbl_BTemp) b
    on a.r=b.r

UPDATE Tbl_ATemp
SET B_Id = #tmp.BId
FROM Tbl_ATemp INNER JOIN #tmp ON Tbl_ATemp.Id = #tmp.AId

DROP TABLE #tmp

干杯:) ..

Cheers :) ..

这篇关于如何从SQL Server中的两个表更新一对一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！