Incrementing datetime field with an update stateme

Question

So I've got a table that contains a number of datetime fields, and I have to increment each date by 1 day as I descend the list. So the dates as they exist would be:

2011-04-19
2011-04-19
2011-04-19
2011-04-19

And I need to run a statement to make them look like:

2011-04-19
2011-04-20
2011-04-21
2011-04-22

So basically, the first row stays the same, the second row gets a day added, the third gets 2 days added, the fourth gets 3 days added, etc.

I've been searching but can't quite figure out what to do. I'd prefer not to use a cursor, so if anyone has any advice it would be much appreciated. Thanks!

Answer 1

You can use session variables:

SET @r := -1;

UPDATE  mytable
SET     mydate = mydate + INTERVAL (@r := @r + 1) DAY;

In SQL Server 2005:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY mydate) rn
        FROM    mytable
        )
UPDATE  q
SET     mydate = DATEADD(d, rn - 1, mydate)