CODEWITHSUNDEEP
mysqlcomparison
Jeremy Sheldon
Jeremy Sheldon

Reputation: 11

MySQL Comparison with Count

I'm trying to update the active field and set it based on how many products have shipped to the customer. But it's not working as intended. I have some records which issueShipped is greater than totalIssues but isActive isn't being set to false.

UPDATE xatm.subscriptions a, 
       (SELECT idSubscription, COUNT(idProduct) issueShipped 
        FROM xatm.subscriptionshipments 
        group by idSubscription) b 
SET a.isActive = (a.totalIssues > b.issueShipped)
WHERE a.idsubscriptions = b.idSubscription
AND a.isComp = 0 AND a.isReoccurring = 0;

Upvotes: 0

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I actually think your current query should work, but in any case an alternative you could try would be an update join:

UPDATE xatm.subscriptions a
INNER JOIN
(
    SELECT idSubscription, COUNT(idProduct) issueShipped 
    FROM xatm.subscriptionshipments 
    GROUP BY idSubscription
) b
    ON a.idsubscriptions = b.idSubscription
SET a.isActive = (a.totalIssues > b.issueShipped)
WHERE 
    a.isComp = 0 AND
    a.isReoccurring = 0;

Upvotes: 1

Related Questions