create table tb1(id1 varchar(21),add1 varchar(41),id2 varchar(12),add2 varchar(12)) insert into tb1 values('1005','beijing','12','zhongguo') insert into tb1 values('1001','a1','1','beijing' ) insert into tb1 values('1002','a2','2','beijing' ) insert into tb1 values('1003','a3','3','beijing') insert into tb1 values('1004','a4','4','nanjing' ) goupdate tb1 set id2 = isnull((select top 1 id1 from tb1 where add1 = t.add2),id2) from tb1 tselect * from tb1drop table tb1/* id1 add1 id2 add2 --------------------- ----------------------------------------- ------------ ------------ 1005 beijing 12 zhongguo 1001 a1 1005 beijing 1002 a2 1005 beijing 1003 a3 1005 beijing 1004 a4 4 nanjing(所影响的行数为 5 行)*/
Try the next statement.update tb1 set tb1.id2 = (select t1.id1 from tb1 t1, tb1 t2 where t1.add1 = t2.add2 AND t2.id1 = tb1.id1) where tb1.id1 in (select t2.id1 from tb1 t1, tb1 t2 where t1.add1 = t2.add2)
insert into tb1 values('1005','beijing','12','zhongguo')
insert into tb1 values('1001','a1','1','beijing' )
insert into tb1 values('1002','a2','2','beijing' )
insert into tb1 values('1003','a3','3','beijing')
insert into tb1 values('1004','a4','4','nanjing' )
goupdate tb1
set id2 = isnull((select top 1 id1 from tb1 where add1 = t.add2),id2)
from tb1 tselect * from tb1drop table tb1/*
id1 add1 id2 add2
--------------------- ----------------------------------------- ------------ ------------
1005 beijing 12 zhongguo
1001 a1 1005 beijing
1002 a2 1005 beijing
1003 a3 1005 beijing
1004 a4 4 nanjing(所影响的行数为 5 行)*/
set tb1.id2 = (select t1.id1
from tb1 t1, tb1 t2
where t1.add1 = t2.add2
AND t2.id1 = tb1.id1)
where tb1.id1 in (select t2.id1
from tb1 t1, tb1 t2
where t1.add1 = t2.add2)