SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating the same table for date.

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by firblazer View Post
    I want to increment the date by a number of weeks, say 4, from the expiryDateTime already in there.
    Code:
    UPDATE media 
       SET expiryDateTime = expiryDateTime + INTERVAL 4 WEEK
     WHERE fkCategoryId = 3
    Quote Originally Posted by firblazer View Post
    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.
    this is similar to the above, give it a try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    For the second part, I wrote:


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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •