declare @A table (R1 int,R2 int) insert into @A select 5,null union all select 7,null union all select 8,nulldeclare @B table (RB int) insert into @B select 4 union all select 9 union all select 13;with m1 as ( select row_number() over (order by (select 1)) as id, * from @A ), m2 as ( select row_number() over (order by (select 1)) as id, * from @B ), m3 as ( select R1,RB from m1 a,m2 b where a.id=b.id )--更新 update @A set R2=b.RB from @A a left join m3 b on a.R1=b.R1 --更新后的结果 select * from @A /* R1 R2 ----------- ----------- 5 4 7 9 8 13 */
--我只是为了显示效果更为明显把数据改了一下而已。--我又改回来了,还是用你给的123,456吧。declare @A table (R1 int,R2 int) insert into @A select 1,null union all select 2,null union all select 3,nulldeclare @B table (R3 int) insert into @B select 4 union all select 5 union all select 6;with m1 as ( select row_number() over (order by (select 1)) as id, * from @A ), m2 as ( select row_number() over (order by (select 1)) as id, * from @B ), m3 as ( select R1,R3 from m1 a,m2 b where a.id=b.id )--更新 update @A set R2=b.R3 from @A a left join m3 b on a.R1=b.R1 --更新后的结果 select * from @A /* R1 R2 ----------- ----------- 1 4 2 5 3 6 */
;with m1 as ( select row_number() over (order by (select 1)) as id, * from A表 ), m2 as ( select row_number() over (order by (select 1)) as id, * from B表 ), m3 as ( select R1,R3 from m1 a,m2 b where a.id=b.id ) select * from m3 不管有多少数据,两个表加上序号,连接查询都是可以的。 关于更新,只要表有主键就可以更新。
update A set R2=R1 +3
declare @A table (R1 int,R2 int)
insert into @A
select 5,null union all
select 7,null union all
select 8,nulldeclare @B table (RB int)
insert into @B
select 4 union all
select 9 union all
select 13;with m1 as
(
select row_number() over (order by (select 1)) as id, * from @A
),
m2 as
(
select row_number() over (order by (select 1)) as id, * from @B
),
m3 as
(
select R1,RB from m1 a,m2 b where a.id=b.id
)--更新
update @A set R2=b.RB
from @A a left join m3 b on a.R1=b.R1
--更新后的结果
select * from @A
/*
R1 R2
----------- -----------
5 4
7 9
8 13
*/
抱歉,格式没调好。A表中有R1,R2. B表中有R3,想实现是UPDATE A表的R2。让A表的R2等于B表的R3。
就是想把B表的 R3列按原顺序放到A表的R2中去。可以实现么?
谢谢啊。
insert into @A
select 1,null union all
select 2,null union all
select 3,nulldeclare @B table (R3 int)
insert into @B
select 4 union all
select 5 union all
select 6;with m1 as
(
select row_number() over (order by (select 1)) as id, * from @A
),
m2 as
(
select row_number() over (order by (select 1)) as id, * from @B
),
m3 as
(
select R1,R3 from m1 a,m2 b where a.id=b.id
)--更新
update @A set R2=b.R3
from @A a left join m3 b on a.R1=b.R1
--更新后的结果
select * from @A
/*
R1 R2
----------- -----------
1 4
2 5
3 6
*/
用存储过程的话我实际表有3000多条记录~
所以我才想像EXEL一样,整列的挪过去 - -|||
;with m1 as
(
select row_number() over (order by (select 1)) as id, * from A表
),
m2 as
(
select row_number() over (order by (select 1)) as id, * from B表
),
m3 as
(
select R1,R3 from m1 a,m2 b where a.id=b.id
)
select * from m3
不管有多少数据,两个表加上序号,连接查询都是可以的。
关于更新,只要表有主键就可以更新。