mysql - MySQL与计数的比较

我正在尝试更新活动字段，并根据向客户发货的产品数量进行设置。但是它没有按预期工作。我有一些记录，其中issueShipped大于totalIssues，但是isActive未被设置为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;

最佳答案

我实际上认为您当前的查询应该可以工作，但是无论如何，您可以尝试的替代方法是更新联接：

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;