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.

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

1 Answer 1

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.