群组功能使用无效

发布于 2024-11-07 05:46:08 字数 249 浏览 0 评论 0原文

我不断收到这样的声明

群组功能使用无效

此查询

 UPDATE users SET users.lastmessage = MAX(messages.id) WHERE users.name ='tom'

我想做的是获取用户表中的lastmessage字段并将其更新为消息表中最大的id，其中名称= tom

我做错了什么？

原文

I keep getting this statement

Invalid use of group function

for this query

 UPDATE users SET users.lastmessage = MAX(messages.id) WHERE users.name ='tom'

What I'm trying to do is take the lastmessage field in the users table and update it to be the largest id in the messages table where the name = tom

What did I do incorrectly?

分享到QQ

分享到微博

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

发布评论

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

走野 2024-11-14 05:46:08

您想要执行子查询来获取用户“tom”的最大 ID，具体操作如下：

UPDATE users 
   SET users.lastmessage = (SELECT MAX(id) FROM Messages WHERE messages.name = users.name) 
 WHERE users.Name = 'tom'

编辑：WHERE 子句仅对正确的用户执行此操作

You want to execute a sub-query to get the maximum Id for the user 'tom' which is done as follows:

UPDATE users 
   SET users.lastmessage = (SELECT MAX(id) FROM Messages WHERE messages.name = users.name) 
 WHERE users.Name = 'tom'

Edit: WHERE clause to only perform this for the correct user

回复收藏 0 原文

笙痞 2024-11-14 05:46:08

不能在 GROUP BY 语句之外使用 MAX 函数。您需要在 UPDATE 查询中执行 SELECT 语句才能正确获取该值。例如：

UPDATE users
SET users.lastmessage = (SELECT MAX(messages.id) AS m_id FROM messages WHERE messages.name = 'Tom')
WHERE users.name = 'Tom'

但是请注意，这有一点限制，因为它仅更新 Tom。如果您想更新每个用户，您可以这样做：

UPDATE users
SET users.lastmessage = (SELECT MAX(messages.id) AS m_id FROM messages WHERE messages.name = users.name)

这将使用他们发布的最后一条消息的日期更新每个用户。

You cannot use the MAX function outside of a GROUP BY statement. You will need to do a SELECT statement inside your UPDATE query to get this value properly. For example:

UPDATE users
SET users.lastmessage = (SELECT MAX(messages.id) AS m_id FROM messages WHERE messages.name = 'Tom')
WHERE users.name = 'Tom'

Notice, however, that this is a bit limited in that it is only updating Tom. If you wanted to update every user, you could do so like this:

UPDATE users
SET users.lastmessage = (SELECT MAX(messages.id) AS m_id FROM messages WHERE messages.name = users.name)

This will update every user with the date of the last message they posted.

回复收藏 0 原文

说好的呢 2024-11-14 05:46:08

UPDATE users, 
 (SELECT MAX(messages.id) as max_message_id FROM messages JOIN users ON (users.user_id = messages.user_id) WHERE users.name LIKE 'Tom') as mm 
 SET users.lastmessage = mm.max_message_id
 WHERE users.name = 'Tom'

我不会说这是解决这个问题的最佳方法，但因为我对你的表结构知之甚少，这似乎正在做你想要的事情。另请记住，我尚未对上述内容进行测试，因此您需要使用自己合适的表和字段来更改表和字段。

UPDATE users, 
 (SELECT MAX(messages.id) as max_message_id FROM messages JOIN users ON (users.user_id = messages.user_id) WHERE users.name LIKE 'Tom') as mm 
 SET users.lastmessage = mm.max_message_id
 WHERE users.name = 'Tom'

I wouldn't say this is the best way to go about solving this, but because I know little about your table structure this seems to be doing what you want. Also keep in mind I haven't tested the above against so you'll need to change the tables and fields with your own proper ones.

回复收藏 0 原文

~没有更多了~