2

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!

Community
  • 1
  • 1
alex
  • 479,566
  • 201
  • 878
  • 984

1 Answers1

3

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.

zombat
  • 92,731
  • 24
  • 156
  • 164
  • Thanks mate, also, clicking through to your site made me realise I read and enjoyed your [WordPress](http://www.phpvs.net/2009/12/08/an-exercise-in-wordpress-integration-or-why-wordpress-sucks/) article a few moons ago, thanks for that! – alex Jul 13 '10 at 00:32
  • No prob, glad it helped. Yeah, I was pretty frustrated with WP when I wrote that. Hah... good times. – zombat Jul 13 '10 at 00:43