我有两张桌子(A 和 B)。我想用表 B 中的值 col3_B 更新 col3_A。 Col3_B 值是按顺序更新的。所以 Col3_A 必须从 col3_B 开始按顺序更新。
表一:
+--------+--------+--------+
| col1_A | col2_A | col3_A |
+--------+--------+--------+
| A | 1 | 5 |
| B | 1 | 3 |
| C | 1 | 2 |
| D | 1 | 1 |
+--------+--------+--------+
表 B:
+--------+
| col3_B |
+--------+
| 6 |
| 7 |
| 8 |
| 9 |
+--------+
要求的结果:
+--------+--------+--------+
| col1_A | col2_A | col3_A |
+--------+--------+--------+
| A | 1 | 6 |
| B | 1 | 7 |
| C | 1 | 8 |
| D | 1 | 9 |
+--------+--------+--------+
问题我的代码只将 col3_B 的前两个值设置为所有 col3_A 列,并且只有两个值重复,例如
+--------+
| Col3_A |
+--------+
| 6 |
| 7 |
| 6 |
| 7 |
+--------+
我的代码:
update A
set A.col2_A = '1', A.col3_A = B.col3_B
from (select ROW_NUMBER() OVER (ORDER BY [col] ASC) AS col3_B
from tableb) B
where A.col1_A in
(
'A',
'B',
'C',
'D'
)
您也可以尝试以下选项。请确保我们订购记录的方式是正确的,因为我不确定这样做的业务逻辑
with temp as
(
select col1_a,col2_a,col3_a ,
row_number() over( order by col2_a ) as rn
from tablea
),
temp1 as
(
select col3_b ,row_number() over( order by col3_b ) as rn
from tableb
)
select temp.col1_a,temp.col2_a,temp.col3_a,temp.rn,temp1.col3_b
into #tt
from temp join temp1
on temp.rn=temp1.rn;
update tablea
set col3_a=( select tt.col3_b from #tt tt where tt.col3_a=tablea.col3_a)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句