This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

limit不能与in/all/any/some连用

在项目中需要在数据库中找到combine_time字段的字符串长度最大的两条记录，然后清空该字段的字符串内容。
一开始写的SQL语句：

UPDATE fault_repair SET combine_time = '' WHERE LENGTH(combine_time) IN 
(SELECT LENGTH(combine_time) FROM fault_repair ORDER BY LENGTH(combine_time) DESC LIMIT 0,2);

执行该语句报错：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’。
说明limit不能与in/all/any/some连用。

-----------------------------------------------------------------分割线----------------------------------------------------------------------------

根据网上查阅的解决方法，可以有两种改法：
① 在in和limit之间再加一层select，隔开了in和limit：

UPDATE fault_repair SET combine_time = '' WHERE LENGTH(combine_time) IN 
	(SELECT LENGTH(temp.combine_time) FROM												<-加了一层select
		(SELECT * FROM fault_repair ORDER BY LENGTH(combine_time) DESC LIMIT 0,2) as temp);	

-- 记得要加别名 as temp，不然报错： Every derived table must have its own alias

②使用inner join与查询自身得到的记录集内联

UPDATE fault_repair SET combine_time = '' WHERE combine_time IN 
	(SELECT combine_time FROM 																		<-需要再加一层select
		(SELECT p1.combine_time FROM fault_repair p1 INNER JOIN 
			(SELECT * FROM fault_repair ORDER BY LENGTH(combine_time) DESC LIMIT 0,2) p2 
		ON p1.id = p2.id)
	as temp);
	-- 记得要加别名 as temp，不然报错： Every derived table must have its own alias

另外注意，update的from子句不能指定目标表，否则报错：You can’t specify target table fault_repair’ for update in FROM clause，因此上面这条sql再加了一层select。

本文仅为个人记录，如有侵权/错误，恳请指出 (╹▽╹)…