1

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
Jhorra
  • 6,233
  • 21
  • 69
  • 123

2 Answers2

3

You have to either join this table to itself, effectively behaving like there are two tables, or select the next date for each record in a subquery. Your query will probably look something like this:

UPDATE EVENTS
SET e.NextDate = (
  SELECT TOP 1 n.EventDate
  FROM EVENTS AS n
  WHERE e.UserId = n.UserId
  AND n.EventDate > e.EventDate
  ORDER BY n.EventDate ASC)
FROM EVENTS AS e

It may not be 100% correct, as I haven't used a windows SQL server in a while. The basic idea is the same as joining the table to another table, except you join it to the same table.

See this answer for more help: https://stackoverflow.com/a/14618713/1160540

Community
  • 1
  • 1
vahanpwns
  • 939
  • 5
  • 12
  • +1 For the self join. Another thing, In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP. – jegtugado Apr 11 '16 at 00:31
  • @Ephraim I had that in mind when I started writing, but forgot to put it in! I'll update my answer... – vahanpwns Apr 11 '16 at 00:33
  • 1
    I hadn't thought about this approach, that's easy! – Jhorra Apr 11 '16 at 01:54
2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I was not aware of this function (I used to code for SQL Server 2008) but it is designed exactly for this task and should be used instead of my answer if it's available. https://msdn.microsoft.com/en-GB/library/hh213125.aspx – vahanpwns Apr 11 '16 at 09:13
  • 1
    I am using Sql Server 2012, so I changed the answer to this one. – Jhorra Apr 12 '16 at 00:46