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

Question

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

MickyT · Accepted Answer · 2014-09-01 20:29:39Z

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;

Mihai · Accepted Answer · 2014-09-01 19:57:34Z

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
– porton

2014-09-01 13:23:58 +00:00
Commented Sep 1, 2014 at 13:23 — porton
– porton, Commented Sep 1, 2014 at 13:23

Stack Exchange Network

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

2 Answers 2

Linked

Hot Network Questions

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

2 Answers 2

Linked

Related

Hot Network Questions