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

发布于 2024-09-12 02:21:47 字数 425 浏览 3 评论 0原文

我想做的是：

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

在我看来，这个语句的语义首先是数据库会启动并为我确定所有表中field的最大值是多少。然后，它会将该值加 1，并将结果值分配给 id 1、3、5、6 和 8 的行的 field 列。看起来很简单够了...

当我尝试运行该查询时，MySQL 窒息了并说：

ERROR 1111 (HY000): Invalid use of group function

你必须使用什么秘密武器才能获得我想要的结果？

问候，维克

原文

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 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...

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?

Regards,
Vic

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抠脚大汉 2024-09-19 02:21:47

尝试

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)

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)

回复收藏 0 原文

埋情葬爱 2024-09-19 02:21:47

为了解决 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)

In order to get around the mysql-error-1093, use a subquery/derived table/inline view:

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)

回复收藏 0 原文

~没有更多了~