如果有相关的ID 可以用
update table1 set xm=b.xm from table1 a,table2 b where a.id=b.id
可是我这两个表 没有相关的字段
用下面的语句 好像更新不了update table1 set xm=b.xm from table1 a,table2 b
update table1 set xm=b.xm from table1 a,table2 b where a.id=b.id
可是我这两个表 没有相关的字段
用下面的语句 好像更新不了update table1 set xm=b.xm from table1 a,table2 b
那你更新什么 。。
--SQL2005,依次对应更新
with cte as
(
select k.xm,o.xm
from
(select rn=ROW_NUMBER()over(order by getdate()),xm from table1 ) k join
(select rn=ROW_NUMBER()over(order by getdate()),xm from table2 ) o on k.rn=o.rn
)update cte
set k.xm=o.xm
with cte as
(
select k.a,o.b
from
(select rn=ROW_NUMBER()over(order by getdate()),xm from table1 ) k join
(select rn=ROW_NUMBER()over(order by getdate()),xm from table2 ) o on k.rn=o.rn
)update cte
set b=a
表1 表2
字段a 字段b
1 a
2 b
3 c
4 d效果就是
表1 表2
字段a 字段b
1 1
2 2
3 3
4 4
select id=identity(int),* into #t2 from 表2update #t2 set 字段b=b.字段a from #t1 a,#t2 b where a.id=b.id
select identity(int,1,1),* into #2 from table2update #2
set b=#1.a
from #1
where #1.id=#2.id truncate table2
insert table2 select b from #2