mysql 查询将字段更新为 max(field) + 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3332026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql query to update field to max(field) + 1
提问by vicatcu
What I want to do is:
我想做的是：
UPDATE table SET field = MAX(field) + 1 WHERE id IN (1, 3, 5, 6, 8);
The semantics of this statement, in my mind, would be first the database would go off and determine for me what the largest value of fieldis in all of table. It would then add 1 to that value, and assign the resulting value to the fieldcolumn of the rows with id1, 3, 5, 6, and 8. Seems simple enough...
在我看来，这个语句的语义首先是数据库将关闭并为我确定field所有table. 然后它会将该值加 1，并将结果值分配给field具有id1、3、5、6 和 8的行的列。看起来很简单......
When I try to run that query though, MySQL chokes on it and says:
但是，当我尝试运行该查询时，MySQL 窒息并说：
ERROR 1111 (HY000): Invalid use of group function
What's the secret sauce you have to use to get the outcome I desire?
为了得到我想要的结果，你必须使用什么秘方？
Regards, Vic
问候， 维克
回答by GWW
Try
尝试
UPDATE TABLE set field = ((SELECT selected_value FROM (SELECT MAX(field) AS selected_value FROM table) AS sub_selected_value) + 1) WHERE id in (1,3,5,6,8)
回答by OMG Ponies
In order to get around the mysql-error-1093, use a subquery/derived table/inline view:
为了绕过mysql-error-1093，使用子查询/派生表/内联视图：
UPDATE table
SET field = (SELECT x.max_field
FROM (SELECT MAX(t.field) + 1 AS max_field
FROM TABLE t
WHERE t.id IN (1,3,5,6,8) ) x)
