UPDATE with a JOIN based on date

Databases
1

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:

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 
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

3

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):

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

so there is no 2.a OR ?

5

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 
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
7

Thanks for both replies. This works beautifully!