mysql更新时,要更新记录中某个区间的数据,只能用WHERE条件来限制了,
用LIMIT只能限制更新多少条!测试如下:
UPDATE t_shop SET shop_code = '1' ORDER BY shop_id DESC LIMIT 5,10
上面这句测试了是错误的,MYSQL的UPDATE语句不能更新限制从第几条到第几条!
错误提示:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10' at line 1
方式一:直接使用mysql的limit语句
只能用下面这样的语句了,要么你就用where条件语句来限制了
UPDATE t_shop SET shop_code = '1' where shop_id>10 ORDER BY shop_id DESC LIMIT 10
方式二:在条件中使用where语句后面跟limit语句限定更新条数
UPDATE t_shop SET shop_code = '1' where shop_id in
(select shop_id from (select shop_id from t_shop where shop_Id>10 limit 0,10) t )
或者
UPDATE t_shop t1,(select * from t_bd_shop where shop_Id>10 limit 0,10
) t2 SET t1.shop_code = '1' where t1.shop_id =t2.shop_id
完整sql语句如下:
UPDATE t_shop SET shop_code = '1' where shop_id in
(select shop_id from (select shop_id from
t_shop where shop_Id>10 limit 0,10) t )
UPDATE t_shop t1,(select * from t_bd_shop
where shop_Id>10 limit 0,10
) t2 SET t1.shop_code = '1'
where t1.shop_id =t2.shop_id