Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Search about emulating the LAG function with user variables. –  Mihai Aug 31 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. –  Pieter Geerkens Aug 31 at 22:58

1 Answer 1

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) ;
share|improve this answer
    
@user46591: Typo corrected for Timeiff. –  Pieter Geerkens Sep 1 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. –  David Spillett Sep 1 at 14:10
    
#1093 - You can't specify target table 'R' for update in FROM clause –  porton Sep 1 at 19:03
1  
@porton, did you try the second update statement? –  ypercube Sep 1 at 20:13
    
@ypercube: Still learning about LATERAL JOIN (ie OUTER APPLY); would that simplify the syntax of the amended query when available? –  Pieter Geerkens Sep 2 at 2:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.