有表 post,里面有两个字段pid,subject
有9万条数据,按pid排序
现在我要把
第1条的subject=第30000条的subject
第2条的subject=第30001条的subject
...
第29999条的subject=第59999条的subject这个SQL语句该怎么写呀?
有9万条数据,按pid排序
现在我要把
第1条的subject=第30000条的subject
第2条的subject=第30001条的subject
...
第29999条的subject=第59999条的subject这个SQL语句该怎么写呀?
除非你的PID是连续的从1 开始正好与顺序相等(中间没有断号),则可以用简单的SQL语句实现update post a inner jion post b on a.pid=b.pid-30000+1 set a.subject=b.subject where a.pid <30000
1:create table tmp1 (id int,pid int,subject varchar(200));
2: create table tmp2 (id int,pid int,subject varchar(200));
3:
declare @ID int;
set @ID:=0;
select @ID:=@ID+1 as ID,pid,subject in tmp1 from tmpcard limit 29999;4:
declare @ID int;
set @ID:=30000;
select @ID:=@ID+1 as ID,pid,subject in tmp2 from tmpcard limit 30000,29999;5:update tmp1 A ,tmp2 B set A.subject=B.subject where a.pid+29999=b.pid6:delete from post,tmp1 where post.pid=tmp.pid;
7:insert into post values select pid,subject from tmp1;8:drop table tmp1,tmp2;
create table tmp2 (id int,pid int,subject varchar(200));
declare @ID int;
set @ID:=0;
select @ID:=@ID+1 as ID,pid,subject in tmp1 from pra_forum_post limit 23999;
declare @ID int;
set @ID:=30000;
select @ID:=@ID+1 as ID,pid,subject in tmp2 from pra_forum_post limit 24000,23999;
update tmp1 A ,tmp2 B set A.subject=B.subject where a.id+29999=b.id
update pra_forum_post,tmp1 set pra_forum_post.subject=tmp1.subject where pra_forum_post.pid=tmp1.pid;
drop table tmp1,tmp2;
create table tmp2 (id int,pid int,subject varchar(200));
declare @ID int;
set @ID:=0;
select @ID:=@ID+1 as ID,pid,subject in tmp1 from pra_forum_post limit 23999;
declare @ID int;
set @ID:=24000;
select @ID:=@ID+1 as ID,pid,subject in tmp2 from pra_forum_post limit 24000,23999;
update tmp1 A ,tmp2 B set A.subject=B.subject where a.id+23999=b.id
update pra_forum_post,tmp1 set pra_forum_post.subject=tmp1.subject where pra_forum_post.pid=tmp1.pid;
drop table tmp1,tmp2;
+-----+---------+
| pid | subject |
+-----+---------+
| 1 | A01 |
| 3 | A02 |
| 31 | A16 |
| 33 | A17 |
| 9 | A05 |
| 11 | A06 |
| 13 | A07 |
| 15 | A08 |
| 7 | A04 |
| 19 | A10 |
| 21 | A11 |
| 23 | A12 |
| 39 | A20 |
| 41 | A21 |
| 25 | A13 |
| 27 | A14 |
| 29 | A15 |
| 35 | A18 |
| 17 | A09 |
| 5 | A03 |
| 37 | A19 |
| 43 | A22 |
| 45 | A23 |
| 47 | A24 |
+-----+---------+
24 rows in set (0.00 sec)mysql> delimiter ;
mysql> delimiter //
mysql> create procedure x()
-> begin
-> DECLARE done INT DEFAULT 0;
-> DECLARE a,b INT;
-> DECLARE c varchar(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM post order by pid limit 10;
-> DECLARE cur2 CURSOR FOR SELECT pid,subject FROM post order by pid limit
10,10;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> OPEN cur2;
->
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
->
-> WHILE done = 0 DO
-> update post set subject=c where pid=a;
->
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
->
-> end;//
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql> call x();
Query OK, 1 row affected (0.05 sec)mysql> select * from post order by
+-----+---------+
| pid | subject |
+-----+---------+
| 1 | A11 |
| 3 | A12 |
| 5 | A13 |
| 7 | A14 |
| 9 | A15 |
| 11 | A16 |
| 13 | A17 |
| 15 | A18 |
| 17 | A19 |
| 19 | A20 |
| 21 | A11 |
| 23 | A12 |
| 25 | A13 |
| 27 | A14 |
| 29 | A15 |
| 31 | A16 |
| 33 | A17 |
| 35 | A18 |
| 37 | A19 |
| 39 | A20 |
| 41 | A21 |
| 43 | A22 |
| 45 | A23 |
| 47 | A24 |
+-----+---------+
24 rows in set (0.00 sec)mysql>