Why would this SQL error with "Invalid use of group function"?

Question

This is a simple query ran when the user presses logout from my website

UPDATE `user_logins` 
   SET `active` = 0 
 WHERE `user_id` = 3 
   AND `datetime` = MAX(`datetime`) LIMIT 1

The user_id value is binded in there with PDO.

I get the following exception thrown

Invalid use of group function

Googling around seems to say that it is because I am using an aggregate function in a WHERE clause.

I also found this question on Stack Overflow, but playing around with HAVING didn't seem to work for me. I tried replacing the AND with HAVING.

How can I change this query to not use the aggregate (or to use HAVING), but still perform the same functionality?

Thanks a bunch!

Answer 1

You could use ORDER BY and LIMIT:

UPDATE `user_logins` SET `active` = 0
WHERE `user_id` = 3
ORDER BY `datetime` DESC
LIMIT 1;

That will put the highest date time for that user first, and the LIMIT 1 will ensure that only the first record gets updated if more than one record matches the query.