2

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.

2
  • 1
    Search about emulating the LAG function with user variables.
    – Mihai
    Aug 31, 2014 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, 2014 at 22:58

1 Answer 1

4

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) ;
5
  • @user46591: Typo corrected for Timeiff. Sep 1, 2014 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, 2014 at 14:10
  • #1093 - You can't specify target table 'R' for update in FROM clause
    – porton
    Sep 1, 2014 at 19:03
  • 1
    @porton, did you try the second update statement? Sep 1, 2014 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, 2014 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.