MySQL Update same table as Select with Having and Group By

Question

I have a Select query which returns me the session IDs of all sessions that have a time less that 8 days old. It works great!

SELECT sessionID FROM session WHERE sessionStatus = 'open' GROUP BY sessionID HAVING MAX(refTime) <= 8;

But, I am trying to update the table so that every record with the session ID that is less than 8 days old has its sessionStatus changed to 'closed'. From stackoverflow I know I can't update a table that I am also selecting from and that Having and Group By are agerate functions that make this more complex.

I tried this, but no dice!

UPDATE session 
SET sessionStatus='closed'
WHERE sessionID = (select * from (SELECT MAX(sessionID) FROM session where sessionStatus = 'open') as t);

I would really appreciate any help!

Answer 1

Here's the workaround I use in cases like this:

CREATE TEMPORARY TABLE tempsessions AS SELECT MAX(sessionID) AS sessionID FROM session where sessionStatus = 'open';
UPDATE session SET sessionStatus = 'closed' WHERE sessionID IN (SELECT sessionID FROM tempsessions);
DROP TEMPORARY TABLE tempsessions;

Answer 2

Try this one also -

UPDATE
  session s1
JOIN
  (SELECT MAX(sessionID) sessionID FROM session WHERE sessionStatus = 'open') s2
    ON s1.sessionID = s2.sessionID
SET
  s1.sessionStatus = 'closed';