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你试下
update a set a.col2=b.col3 from (select *,row_number()over(order by newid()) as rn from t1) as a join (select *,row_number()over(order by newid()) as rn from t2 )as b on a.rn=b.rn 试试这个,自己通过RN 建立一个对应关系。
提示一下: 1. t2 表中如果存在 col2 是重复的,你需要 group by ,或 distinct 一下。 2. t2 表中的不重复数据,是否比t1 的行数要多? 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 JOIN (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM t2)temp2 ON temp1.RN=temp2.RN 那就内连接,t2有多少就更新多少行到t1去 SQL2005+有效,SQL2000的话,另外处理
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你试下
join (select *,row_number()over(order by newid()) as rn from t2 )as b
on a.rn=b.rn
试试这个,自己通过RN 建立一个对应关系。
1. t2 表中如果存在 col2 是重复的,你需要 group by ,或 distinct 一下。
2. t2 表中的不重复数据,是否比t1 的行数要多?
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
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的话,另外处理