Question

我有一个选择查询, 返回所有会话的会话代号, 时间小于八天。效果很好!

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

但是,我正试图更新表格,以便每张有不到8天的会话代号的记录都能够将其会话状态改变为关闭。从堆积流中,我知道我无法更新一个我也从中选择的表格,而Cade and Group By是使这个过程更加复杂的老化功能。

我试过了,但没有骰子!

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

我真的很感激你的帮助!

Answer 1

我在此使用类似情况的工作方式:

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

这个也试试看

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

友情链接