Updating the same table for date.
I have a media table:
id, endDateTime, expiryDateTime, fkCategoryId
1, 2013-02-01 01:00:15, 2013-5-17 01:00:15, 3
2, 2013-03-04 05:00:15, 2013-5-13 05:00:15, 3
3, 2013-03-11 03:30:15, 2013-5-11 03:30:15, 4
I want to increment the date by a number of weeks, say 4, from the expiryDateTime already in there. Here is what I do:
UPDATE media SET expiryDateTime = DATE_ADD((SELECT expiryDateTime FROM media WHERE fkCategoryId = 3), INTERVAL 4 WEEK) WHERE fkCategoryId = 3
But MYSQL says: You can't specify target table 'media' for update in FROM clause.
I guess I cannot use the same table. What can I do?
I would also like to check for NULL in the expiryDateTime field or 1000-01-01 00:00:00 and update it with endDateTime plus 4 weeks.
What can I do?