mysql查询将字段更新为max(field)+ 1 [英] mysql query to update field to max(field) + 1

问题描述

我想做的是:

UPDATE table SET field = MAX(field) + 1 WHERE id IN (1, 3, 5, 6, 8);

在我看来，该语句的语义首先是数据库将关闭，并为我确定在所有table中最大的field值是什么.然后，它将对该值加1，并将结果值分配给具有id 1、3、5、6和8的行的field列.似乎很简单...

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 field is in all of table. It would then add 1 to that value, and assign the resulting value to the field column of the rows with id 1, 3, 5, 6, and 8. Seems simple enough...

不过，当我尝试运行该查询时，MySQL对其进行了扼制并说:

When I try to run that query though, MySQL chokes on it and says:

ERROR 1111 (HY000): Invalid use of group function

要获得我想要的结果，您必须使用什么秘诀?

What's the secret sauce you have to use to get the outcome I desire?

关于， 维克

推荐答案

尝试

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)

这篇关于mysql查询将字段更新为max(field)+ 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！