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.

  • 1
    Search about emulating the LAG function with user variables.
    – Mihai
    Aug 31 '14 at 22:45
  • 2
    Don't bother learning CURSORS; in over 30 years as a database applications developer I have never once used one, except by way of demonstrating that they were unnecessary and non-performant. Aug 31 '14 at 22:58

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

SET Duration = TimeDiff(
         ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1)
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:

  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) ;
  • @user46591: Typo corrected for Timeiff. Sep 1 '14 at 3:03
  • That would be more efficient with the "window" functions available in DBs like MS SQL Server and postgres, specifically LAG(). This can be emulated in mySQL though (see this answer for an example: stackoverflow.com/a/11316349/114292) so might be worth considering. Sep 1 '14 at 14:10
  • #1093 - You can't specify target table 'R' for update in FROM clause
    – porton
    Sep 1 '14 at 19:03
  • 1
    @porton, did you try the second update statement? Sep 1 '14 at 20:13
  • @ypercube: Still learning about LATERAL JOIN (ie OUTER APPLY); would that simplify the syntax of the amended query when available? Sep 2 '14 at 2:15

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.