求批量更新MySQL语句

2010-10-14 03:50:47 304 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

例题讲解拉格朗日乘子法、线性可分支持向量机(SVM)的推导支持向量机(Support Vector Machine, SVM)的简单推导及例题阅读详情

有表 post,里面有两个字段pid,subject
有9万条数据，按pid排序
现在我要把
第1条的subject=第30000条的subject
第2条的subject=第30001条的subject
...
第29999条的subject=第59999条的subject

这个SQL语句该怎么写呀？

...全文

SpringBoot文件上传实战：MultipartFile重复读取的3种方法（附避坑指南）本文详细介绍了SpringBoot中处理MultipartFile重复读取的3种实用方法，包括字节数组缓存法、临时文件法和流重置法，并提供了避坑指南和性能优化技巧。特别适合需要多次读取上传文件内容的开发者，帮助解决InputStream流只能读取一次的问题。阅读详情

伏伏

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

wwwwb 2010-10-14

打赏
举报

zuoxingyu的方法不错

ACMAIN_CHM 2010-10-14

打赏
举报

mysql> select * from post;

+-----+---------+

| 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>

伏伏 2010-10-14

打赏
举报

create table tmp1 (id int,pid int,subject varchar(200));
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;

伏伏 2010-10-14

打赏
举报

谢谢你zuoxingyu。thank you very much！！！

伏伏 2010-10-14

打赏
举报

create table tmp1 (id int,pid int,subject varchar(200));
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;

zuoxingyu 2010-10-14

打赏
举报

是的。

人工做一个按顺序排列的字段出来。这是主要思路。

伏伏 2010-10-14

打赏
举报

那如果表中不止这两个字段就在下面的代码里面自己加是吧？

[Quote=引用 8 楼 zuoxingyu 的回复:]
提供个思路吧；
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,subjec……
[/Quote]

zuoxingyu 2010-10-14

打赏
举报

以为你的pid不连续，那么人为制造一个连续的ID

再根据这个ID去去30000条，再进行修改。

代码不对的地方自己修正一下。

zuoxingyu 2010-10-14

打赏
举报

提供个思路吧；
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.pid

6:delete from post,tmp1 where post.pid=tmp.pid;
7:insert into post values select pid,subject from tmp1;

8:drop table tmp1,tmp2;

伏伏 2010-10-14

打赏
举报

[Quote=引用 4 楼 acmain_chm 的回复:]
9万条数据，如果用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
[/Quote]
acmain_chm大哥，帮助写个吧？谢谢啦！

伏伏 2010-10-14

打赏
举报

谁能帮助写一个呀。PID不是连续的。

伏伏 2010-10-14

打赏
举报

ACMAIN_CHM 2010-10-14

打赏
举报

9万条数据，如果用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

伏伏 2010-10-14

打赏
举报

[Quote=引用 2 楼 zuoxingyu 的回复:]
update post A ,post B set A.subject=B.subject where a.pid+29999=b.pid
[/Quote]
这个貌似不对，因为pid不是连续的。

zuoxingyu 2010-10-14

打赏
举报

update post A ,post B set A.subject=B.subject where a.pid+29999=b.pid

伏伏 2010-10-14

打赏
举报

在线等啊等啊。

求批量更新MySQL语句

相关推荐