MySQL Update same table as Select with Having and Group By
mysql
Solution
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;
Problem
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!