Updating the same table for date

Databases
1

Hi!

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?

Thanks.

2 
UPDATE media 
   SET expiryDateTime = expiryDateTime + INTERVAL 4 WEEK
 WHERE fkCategoryId = 3

this is similar to the above, give it a try

3

Thanks!

For the second part, I wrote:

UPDATE media SET expiryDateTime = IFNULL(expiryDateTime + INTERVAL 4 WEEK, endDateTime + INTERVAL 4 WEEK) WHERE fkPurposeCategoryId = 3