SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE with a JOIN based on date

    We have some screwy data because of a scenario we didn't foresee (shocked face!). So we need to go through the database and change some dates. In this case we need to change expiration dates for a subscription. I have two tables setup like so:

    Code:
    members                  orders
    --------------------     --------------------
    member_id                member_id
    exp_date                 order_date
                             order_amount
                             order_desc
    The order_amount will be either 16.00 (for a 1 year subscription), 42.00 (for 3 years), or 62.00 (for 5 years).

    The order_desc tells me, literally, '1 year membership', '3 year membership', or '5 year membership'.

    I need help with a query that will update the exp_date based on 2 things:

    1. If orders.order_date is greater than members.exp_date based on member_id

    2. Then update members.exp_date to be NOW() + the number of years in orders.order_desc

    2a. OR, update members.exp_date to be NOW() + some use of orders.order_amount

    There seems to be a lot going on here, so if you have a suggestion or query I could try, I would be thrilled!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    UPDATE members
    INNER
      JOIN orders
        ON orders.member_id = members.member_id
       SET members.exp_date =
           CASE WHEN orders.order_date > members.exp_date  
                THEN CURRENT_DATE + INTERVAL LEFT(orders.order_desc,1) YEAR
                ELSE CURRENT_DATE + INTERVAL somefunction(orders.order_amount) YEAR
            END
    should be okay if you can come up with something for "somefunction" which you kind of glossed over in your description
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This looks great r937, thank you.

    The part I glossed over was an alternative, but since you showed me how to use the INTERVAL LEFT() function, the issue of updating by years should be solved.

    I tried your suggestion as you wrote it (minus the ELSE case):
    Code:
    UPDATE members
    INNER
      JOIN orders
        ON orders.member_id = members.member_id
       SET members.exp_date =
           CASE WHEN orders.order_date > members.exp_date  
                THEN CURRENT_DATE + INTERVAL LEFT(orders.order_desc,1) YEAR
            END
    And MySQL returned "#1048 - Column 'exp_date' cannot be null".

    This is true, though I'm not sure why it throws the error.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ggeiger View Post
    ... (minus the ELSE case)
    so there is no 2.a OR ?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, there is NO 2a OR.

    Your method of using CURRENT_DATE + INTERVAL LEFT(orders.order_desc,1) YEAR is all I need for that.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    UPDATE members
    INNER
      JOIN orders
        ON orders.member_id = members.member_id
       SET members.exp_date = CURRENT_DATE + INTERVAL LEFT(orders.order_desc,1) YEAR
     WHERE orders.order_date > members.exp_date
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for both replies. This works beautifully!


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
  •