MySQL Using result from one query in another

Question

When a user buys a license for my product I'm trying to add the number of days remaining (if he has any) to the new 30-day period.

SELECT
    SUM(DATEDIFF(access_expires, CURDATE())) + 30 as access_remaining
FROM wp_woocommerce_downloadable_product_permissions
WHERE
    user_email = 'user@mail.com' AND
    product_id = 8 AND access_expires IS NOT NULL;

UPDATE wp_woocommerce_downloadable_product_permissions
SET
    access_expires = DATE_ADD(CURDATE(), access_remaining)
WHERE
    user_email = 'user@mail.com' AND
    product_id = 8 AND
    access_expires IS NULL

It seems like I can't use access_remaining from my previous query in my second query. Please help, if possible without using join. Using mariadb if it means anything.

You can't reuse an alias like that. I think the closest thing to what you have in mind would be an updatable CTE, which however MySQL does not support. — Tim Biegeleisen, Sep 25 '17 at 00:15
Isn't this exactly the same thing as `SET access_expires = DATE_ADD(access_expires,INTERVAL 30 DAY)`? — Michael - sqlbot, Sep 25 '17 at 00:26

score 1 · Answer 1 · answered Sep 25 '17 at 00:13

You seem to have imposed odd restrictions on this problem. Are you using strictly /usr/bin/mysql, and cannot use e.g. python or java? Can you assign to a temp variable with := ? Why no JOIN?

Consider using UPDATE with correlated sub-query, as in MySQL/SQL: Update with correlated subquery from the updated table itself .

You chose a perfectly sensible way to model the bookkeeping, by maintaining a calendar expiry date. But consider modeling in a different way: you might store (begin, end) dates for which a customer is paid up. You might find it makes some queries easier to phrase.

score 1 · Answer 2 · answered Sep 25 '17 at 00:34

Notice the use of a "user variable" @ar:

SELECT
    @ar := SUM(DATEDIFF(access_expires, CURDATE())) + 30 as access_remaining
FROM wp_woocommerce_downloadable_product_permissions
WHERE
    user_email = 'user@mail.com' AND
    product_id = 8 AND access_expires IS NOT NULL;

UPDATE wp_woocommerce_downloadable_product_permissions
SET
    access_expires = DATE_ADD(CURDATE(), @ar)
WHERE
    user_email = 'user@mail.com' AND
    product_id = 8 AND
    access_expires IS NULL

I suggest that SUM does not make sense in this context.

Wouldn't it be simpler to just do this?

UPDATE wp_woocommerce_downloadable_product_permissions
SET
    access_expires = GREATEST(access_expires, CURDATE()) + INTERVAL 30 DAY
WHERE
    user_email = 'user@mail.com' AND
    product_id = 8 AND
    access_expires IS NULL

MySQL Using result from one query in another

2 Answers2