sql query help? Sql newbie question

I keep getting this stateme开发者_StackOverflow社区nt "Invalid use of group function"

for this query

 mysql_query("UPDATE users SET users.lastmessage = MAX(messages.id) WHERE users.name ='tom'") 
or die(mysql_error());  

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

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

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.

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.