Geek asks and answers

MySQL updates the same table as Select With Availability and Group By

I have a Select query that returns me the session identifiers of all sessions that have a time of less than 8 days. 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 each record with a session ID that is less than 8 days has its own sessionStatus changed to "closed". From stackoverflow, I know that I cannot update the table that I also select, and that the Have and Group By functions are agate functions that make this more complicated.

I tried this, but not cubes!

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!

+3
source share
2 answers

Here is a workaround that I use in the following cases:

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;
+2

-

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

More articles:


All Articles