Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

errorsMySQLsubquery

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='статистика для каждого отдельного юзера';

Best Answer

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;