MySQL Update based on the previous row

[erikcw]

Hi,

I have a table of timestamps. I am trying to modify them all with DATE_ADD so they are each a day from one another.

I know I could script this out in php - but I am usre there is a way to do it in SQL and I want to use phpMyAdmin to ekecute the statement...

Basically I want to:

UPDATE table SET time=DATE_ADD(....time from previous row..., '0:00', YEAR_MONTH).

I was thinking of maybe using a sub query with LAST_INSERT_ID, but since this is an UPDATE and not an isnert I'm not sure if it will work.

Any ideas?

[coopster]

About the only option I can think of would be an auto_incremented ID. The logic behind it would be to UPDATE the row with the value from the previous ID (this row's ID - 1). Create a temporary table or a copy of your *live* table and play around with that idea (using a subquery). Post your SQL statement here if you get a moment and we'll see if we can assist (if we get a moment ;) ).