Invalid use of group function

Question

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?

Answer 1

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

Answer 2

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.