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!

PLease try this solution: [http://stackoverflow.com/questions/8127257/mysql-update-subset-having/22638878#22638878][1] Regards Piotr [1]: http://stackoverflow.com/questions/8127257/mysql-update-subset-having/22638878#22638878 — Piotr Idzikowski, Mar 25 '14 at 15:23

score 4 · Answer 1 · answered May 23 '12 at 14:32

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;

score 2 · Answer 2 · answered May 23 '12 at 14:56

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';

answered May 23 '12 at 14:56

Devart

119,203
23
166
186

MySQL Update same table as Select with Having and Group By

2 Answers2