MySQL 与 Count 的比较

Question

我正在尝试更新事件字段并根据已运送给客户的产品数量进行设置。但它没有按预期工作。我有一些记录，其中 issuesShipped 大于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;

Answer 1

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

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;