Update table from itself, but only top result

So if we have a table with the following:

Id INT
EventDate DATETIME
NextDate DATETIME
UserId INT

I want to update the next date value from the same table and set the NextDate value to the date of the next entry related to that user.

Below is the basic query, but I'm not sure how to tell it to update it from the next occurring EventDate

UPDATE 
EVENTS
SET
NextDate = n.EventDate
FROM 
EVENTS AS n
WHERE
EVENTS.UserId = n.UserId

Answers 1

  • I would use the lead() function (available in SQL Server 2012+):

    with toupdate as (
          select e.*,
                 lead(eventdate) over (partition by userid order by eventdate) as next_eventdate
          from e
         )
    update toupdate
        set nextdate = next_eventdate;
    

    Note: this should be much more efficient than alternative methods using joins, correlated subqueries, or apply.


Related Articles