更新分页中符合条件的某几条数据问T,不是全部更新,只是分页中符合条件的数据, update tb_gb_post as post set post.is_new=0
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' order by post.create_time ASC limit 1,9;
这样显然不能更新, update ** limit ?; 只可加一个条件,不可加二个。
也不可以
先找到条数,然后再更新(注意是count 和 limit 分页联用了)
select count(post.id) into numLimit from tb_gb_post as post
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9'
and post.is_new = 1 order by post.create_time ASC limit 1,100; 然后再更新:
update *** order by post.create_time ASC limit numLimit;
如上所述,难道我只能用游标,在游标中来一条条更新????????? 注: 不可用临时表,也不能用in mysql 不支持在subquery 中用limit......
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' order by post.create_time ASC limit 1,9;
这样显然不能更新, update ** limit ?; 只可加一个条件,不可加二个。
也不可以
先找到条数,然后再更新(注意是count 和 limit 分页联用了)
select count(post.id) into numLimit from tb_gb_post as post
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9'
and post.is_new = 1 order by post.create_time ASC limit 1,100; 然后再更新:
update *** order by post.create_time ASC limit numLimit;
如上所述,难道我只能用游标,在游标中来一条条更新????????? 注: 不可用临时表,也不能用in mysql 不支持在subquery 中用limit......
解决方案 »
- 仍然是新手问题:
- 更新innodb 类型的表 :出现Lock wait timeout exceeded; try restarting transaction
- 求高手指点两个sql查询的写法
- 问一多级查询语句
- C++ 调用 mysql 中的问题
- 关于mysql的驱动问题
- 如何将 现有的表 倒入 MYSQL的数据库中.
- 请问那儿可以下载到 中文动态SQL 书,我马上给分!100
- MySQL无法启动,发生系统错误1067
- 求一mysql存储过程,望大神们帮解决下,给出自己所有的分
- 如何要把一个byte[]数组存入postgresql中,以何种数据类型存入,又如何读出呢 求解
- 关于 MySQL 中小于1的浮点数的问题
http://topic.csdn.net/u/20090421/18/5c4232c4-fd72-43f3-98ea-af4c21f0e169.html?seed=1608006921
6言,,,100分同样给予,,,,,,
-> id int primary key,
-> col int
-> );
Query OK, 0 rows affected (0.14 sec)mysql>
mysql> insert into t2 values
-> (1,2),
-> (2,4 ),
-> (3,6 ),
-> (4,8 ),
-> (5,10 ),
-> (6,12 ),
-> (7,14 ),
-> (8,16 ),
-> (9,18 ),
-> (10,20),
-> (11,22),
-> (12,24),
-> (13,26),
-> (14,28),
-> (15,30),
-> (16,32),
-> (17,34),
-> (18,36),
-> (19,38),
-> (20,40),
-> (21,42),
-> (22,44),
-> (23,46),
-> (24,48);
Query OK, 24 rows affected (0.05 sec)
Records: 24 Duplicates: 0 Warnings: 0mysql> select * from t2 order by id limit 15,5;
+----+------+
| id | col |
+----+------+
| 16 | 32 |
| 17 | 34 |
| 18 | 36 |
| 19 | 38 |
| 20 | 40 |
+----+------+
5 rows in set (0.00 sec)mysql> update t2,(select id from t2 order by id limit 15,5) b
-> set t2.col=-t2.col
-> where t2.id=b.id;
Query OK, 5 rows affected (0.08 sec)
Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from t2 order by id limit 13,10;
+----+------+
| id | col |
+----+------+
| 14 | 28 |
| 15 | 30 |
| 16 | -32 |
| 17 | -34 |
| 18 | -36 |
| 19 | -38 |
| 20 | -40 |
| 21 | 42 |
| 22 | 44 |
| 23 | 46 |
+----+------+
10 rows in set (0.00 sec)mysql>
2、取得后,更新这一页的记录
update tt a inner join (select id,f1 from tt order by id limit
[每页有多少条记录]*[当前页数-1],[每页有多少条记录]) b
on a.id=b.id set a.f1=b.f1
当要共有几条记录时,不可用,,,,
select count(post.id) from tb_gb_post as post
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' and post.is_new=1 order by post.create_time ASC limit 1,100;当 count(id) limit *; 即接一个参数时,可以求得总记录条数,
当 count(id) limit *,*; 此时不报错,但是返回结果为空,不能得到正确的数值。
是mysql的语法限制?了解的兄弟给讲解下。
ACMAIN_CHM 兄弟的解法:
mysql> update t2,(select id from t2 order by id limit 15,5) b
-> set t2.col=-t2.col
-> where t2.id=b.id;
应该是同样的limit语法限制(个人猜测), limit 15,5时是正确的,
如果传入二个参数则会出问T, limit pageStart,pageLimit; (此变量均已经声明且取值正确)
个人解决方法:(存储过程)
sp_gb_postNew_update`(in gbId char(32),in gbType int,in pageStart int,in pageLimit int)
BEGIN
set @gbId = gbId;
set @gbType = gbType;
set @pageStart = pageStart;
set @pageLimit = pageLimit;
SET @listPost=CONCAT('update tb_gb_post post,(select pos.id from tb_gb_post as pos
where pos.postgb_id= ? and pos.is_new=1
and pos.status <> 1 and pos.type =? order by pos.create_time ASC limit ?,?) po
set post.is_new=0
where post.id=po.id');
PREPARE _stmt FROM @listPost;
execute _stmt using @gbId,@gbType,@pageStart,@pageLimit;
deallocate prepare _stmt; END$$
是mysql的语法限制?
你在MYSQL中运行一下,看看结果是否正确
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' and post.is_new=1
order by post.create_time ASC limit 0,6;
如上所取时,得到了所有的结果 12(记录中共12条符合条件)
如果 limit 1,6; 所获得结果为空,也不报错。
另一情况:
select id from tb_gb_post as post
where post.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' and post.is_new=1
order by post.create_time ASC limit 0,6;
此时所得为 6条符合条件的记录。
如下时,报错:
set @num = 13;
set @lim = 5;
update tb_gb_post post,(select pos.id from tb_gb_post as pos
where pos.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' and pos.is_new=1
and pos.status <> 1 and pos.type =3 order by pos.create_time ASC limit @num,@lim) po
set post.is_new=0 where post.id=po.id;当@num,@lim为 确定的数值时(即 limit 13,5时),正确运行,
2、要用动态运行SQL语句方式才行,
PREPARE
EXECUTE
这种形式,直接运行是错误的
建议直接在你的程序中把这两个值填上。如果在store procedure 中则可以如下
PREPARE stmt1 FROM 'update tb_gb_post post,(select pos.id from tb_gb_post as pos where pos.postgb_id='c7a37d7b7ec4102c9ce552487571d9e9' and pos.is_new=1 and pos.status <> 1 and pos.type =3 order by pos.create_time ASC limit ?,?) po set post.is_new=0 where post.id=po.id';set @num = 13;
set @lim = 5; EXECUTE stmt1 USING @num, @lim;