Mysql – Difference data between adjacent rows

cursorsMySQLselect

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column.

Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not set when inserting new rows to the table. It is calculated afterward.

I calculate the "duration" of a row R as the interval (in seconds) between R.ReqTime and N.ReqTime where N is the next row (first row, inserted later the current row).

Thus for each row R (except of the last inserted row, as for it N is undefined) we have Duration value.

See pseudocode for updating the table with the correct Duration value (where R is the current row and N is the next (inserted later) row):

UPDATE Visits SET R.Duration=TIMEDIFF(N.ReqTime, R.ReqTime) WHERE R.Duration IS NULL

Should I use cursors to solve this problem? Or are MIN/MAX/ORDER BY fine?

I am not yet comfortable with cursors.

MySQL 5.

Best Answer

This SQL works fine in SQL Server (with appropriate syntax modifications):

UPDATE Visits
SET Duration = TimeDiff(
         ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1)
        ,R.ReqTime
    )
FROM Visits R ;

Unfortunately, the syntax hits a limitation of MySQL that an updated table cannot be referenced (again) in the WHERE clause of the UPDATE. A common workaround is to rewrite with a JOIN:

UPDATE Visits R
  JOIN Visits U
    ON U.pk = ( SELECT N.pk                 -- the Primary Key of the table 
                FROM Visits N 
                WHERE N.ReqTime > R.ReqTime 
                ORDER BY N.ReqTime 
                LIMIT 1
              )
SET R.Duration = TimeDiff(U.ReqTime, R.ReqTime) ;
Related Question