mysql查询将字段更新为max(field)+ 1

本文介绍了mysql查询将字段更新为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)