mysql update select

介绍

表结构为：

CREATE TABLE `rule_base）` (
  `pk_rule_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `rule_name` varchar(64) DEFAULT NULL COMMENT '名称',
  `rule_status` smallint(6) DEFAULT NULL COMMENT '状态：0有效,1无效',
  PRIMARY KEY (`pk_rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=573 DEFAULT CHARSET=utf8;

sql:

UPDATE rule_base
SET rule_status = 1
where rule_name ='xxx' ORDER BY createtime limit 1

没毛病；

然后试了下另一个sql

UPDATE rule
SET rule_status = 1
where rule_name ='xxx' ORDER BY createtime limit 1,1

这样就会报错
[Err] 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 '1' at line 3

再试：

UPDATE t_rule_base
SET rule_status = 1
where pk_rule_id in 
(SELECT pk_rule_id from t_rule_base where rule_name like '天%' ORDER BY createtime limit 1,2)

还是报错...貌似内层不能有limit子句
[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

再来，加了个select嵌套：

UPDATE t_rule_base
SET rule_status = 1
where pk_rule_id in (
select * from (SELECT pk_rule_id from t_rule_base where rule_name like '天%' ORDER BY createtime limit 1,2) t)

成了！！！

顺便说下这样也是不行的：

UPDATE (SELECT pk_rule_id from t_rule_base where rule_name like '天%' ORDER BY createtime limit 1,2) t
SET rule_status = 1