0

As an attempt to answer Difference data between adjacent rows

UPDATE Visits R
SET Duration = TIMEDIFF(
        ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID LIMIT 1),
        R.ReqTime
    )
WHERE R.Duration IS NULL

leads to error #1093 - You can't specify target table 'R' for update in FROM clause.

What to do?

MySQL 5. Here is the Visits table:

CREATE TABLE `Visits` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Session` int(10) unsigned NOT NULL COMMENT 'VisitsSessions',
  `EventKind` enum('search','subsectionList','show','seance','like','superprice','toursList','abonementsList','abonement','popular','sales','tab','mainpage','other') NOT NULL COMMENT 'Also ''purchase'' in Bravo::Visits::Query',
  `ReqTime` datetime NOT NULL,
  `Duration` int(11) DEFAULT NULL COMMENT 'считается ПОТОМ, не при INSERT',
  PRIMARY KEY (`ID`),
  KEY `User` (`Session`),
  KEY `EventKind` (`EventKind`),
  KEY `LoadedTime` (`ReqTime`),
  KEY `Duration` (`Duration`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 COMMENT='статистика для каждого отдельного юзера';
1

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;
0
UPDATE visits v JOIN 
(SELECT A.id, (TIME_TO_SEC(B.ReqTime) - TIME_TO_SEC(A.ReqTime)) AS timedifference
FROM visits A CROSS JOIN visits B
WHERE B.id IN (SELECT MIN(C.id) FROM visits  C WHERE C.id > A.id)
ORDER BY A.id ASC)x
ON v.id=x.id
SET v.Duration=x.timedifference

FIDDLE

  • REqTime -> ReqTime – porton Sep 1 '14 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.