How to update a record based on the value of the next record in SQL Server 2008

Question

I like updating the column based on the value of the following record:

resid   startdate   enddate     weekday hours
-----------------------------------------------
2980    2013-09-23  2014-12-31      1     6
2980    2013-09-23  2014-12-31      2     6
2980    2013-09-23  2014-12-31      3     6
2980    2013-09-23  2014-12-31      4     6
2980    2013-09-23  2014-12-31      5     6
2980    2015-01-01  NULL            1     6,8
2980    2015-01-01  NULL            2     6,8
2980    2015-01-01  NULL            3     6,8
2980    2015-01-01  NULL            4     6,8
2980    2015-01-01  NULL            5     6,8
2980    2015-07-01  NULL            1     6
2980    2015-07-01  NULL            2     6
2980    2015-07-01  NULL            3     6
2980    2015-07-01  NULL            4     6
2980    2015-07-01  NULL            5     6

I like to update the NULL value in the column enddate. It should get the value of the following startdate - 1 day.

For example, for all records from startdate2015-01-01, the column enddateshould be updated on 2015-07-01 - 1 day.

Does anyone have a solution?

Answer 1

update TableName set enddate = dateadd(day,1,startdate)

Answer 2

One way to do this is with a subquery:

UPDATE t1
SET enddate = (SELECT TOP 1 DATEADD(DAY, -1, startdate) 
               FROM YourTable t2 
               WHERE t1.startdate < t2.startdate 
               ORDER BY t2.startdate)
FROM YourTable t1
WHERE t1.enddate is null