日期:2014-05-18 浏览次数:20604 次
create table tb1(id int,city varchar(10)) insert into tb1 values(1 , null) insert into tb1 values(2 , null) insert into tb1 values(3 , null) insert into tb1 values(4 , null) insert into tb1 values(5 , null) insert into tb1 values(6 , null) insert into tb1 values(7 , null) insert into tb1 values(8 , null) insert into tb1 values(9 , null) create table tb2(id int,city varchar(10)) insert into tb2 values(1 ,'北京') insert into tb2 values(2 ,'上海') insert into tb2 values(3 ,'南京') go update tb1 set city = tb2.city from tb1 , tb2 where (tb1.id - 1) % 3 = tb2.id - 1 select * from tb1 drop table tb1 , tb2 /* id city ----------- ---------- 1 北京 2 上海 3 南京 4 北京 5 上海 6 南京 7 北京 8 上海 9 南京 (所影响的行数为 9 行) */
------解决方案--------------------
--如果你的序号不是自增一,出现断号
--sql 2000用子查询.
create table tb1(id int,city varchar(10)) insert into tb1 values(1 , null) insert into tb1 values(2 , null) insert into tb1 values(3 , null) insert into tb1 values(4 , null) insert into tb1 values(5 , null) insert into tb1 values(6 , null) insert into tb1 values(7 , null) insert into tb1 values(8 , null) insert into tb1 values(9 , null) create table tb2(id int,city varchar(10)) insert into tb2 values(1 ,'北京') insert into tb2 values(2 ,'上海') insert into tb2 values(3 ,'南京') go update tb1 set city = tb2.city from tb1 , tb2, (select t.* , px=(select count(1) from tb1 where id < t.id) from tb1 t) tb3, (select t.* , px=(select count(1) from tb2 where id < t.id) from tb2 t) tb4 where tb1.id = tb3.id and tb2.id = tb4.id and (tb3.px - 1) % 3 = tb4.px - 1 select * from tb1 drop table tb1 , tb2 /* id city ----------- ---------- 1 北京 2 上海 3 南京 4 NULL 5 上海 6 南京 7 NULL 8 上海 9 南京 (所影响的行数为 9 行) */