22,207
社区成员
发帖
与我相关
我的任务
分享
UPDATE temp1
SET temp1.col2=temp2.col3
FROM(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM t1)temp1
JOIN (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM t2)temp2 ON temp1.RN=temp2.RN
那就内连接,t2有多少就更新多少行到t1去
SQL2005+有效,SQL2000的话,另外处理
update y
set y.col2 = x.col2
from
(select ROW_NUMBER() over(order by id ) rn , * from t1) y ,
(select ROW_NUMBER() over(order by col2 ) rn , col2 from t2 group by col2 ) x
where x.rn = y.rn
UPDATE temp1
SET temp1.col2=temp2.col3
FROM(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM t1)temp1
LEFT JOIN (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM t2)temp2 ON temp1.RN=temp2.RN
你试下