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