MySQL 子查询返回多于 1 行错误

Question

我在下面的 mysql 查询中遇到错误。请注意，我的子查询应该返回 1627862 个结果。有没有办法通过维护子查询的所有条件来解决这个错误？

UPDATE SUBSCRIPTION_LOG 
SET SUBSCRIPTION_STATUS='D', 
    DEACTIVATION_DATE=NOW(), 
    DEACTIVATION_CHANNEL='SYSTEM' 
WHERE SUBSCRIPTION_STATUS ='A' 
AND SHORT_CODE='22222' 
AND MSISDN =(SELECT MSISDN 
             FROM SUBSCRIPTION 
             WHERE DATEDIFF(NOW(),`ACTIVATION_DATE`) > LAST_CHARGED_VALIDITY 
             AND OFFER_CODE NOT IN ('CAT_228','CAT_229','CAT_232','CAT_233') 
             AND SHORT_CODE = '22222');

ERROR 1242 (21000): Subquery returns more than 1 row

Answer 1

您的 MSISDN commpare 中的等于 = 期望您的子查询返回单个值，但子查询返回多行。为了使您的查询有效，请将 = 更改为 ìn，如下所示

UPDATE SUBSCRIPTION_LOG SET SUBSCRIPTION_STATUS='D', DEACTIVATION_DATE=NOW(), 
DEACTIVATION_CHANNEL='SYSTEM' WHERE SUBSCRIPTION_STATUS ='A' 
AND SHORT_CODE='22222' 
AND MSISDN in (SELECT MSISDN 
            FROM SUBSCRIPTION 
         WHERE DATEDIFF(NOW(),`ACTIVATION_DATE`) > LAST_CHARGED_VALIDITY 
         AND OFFER_CODE NOT IN ('CAT_228','CAT_229','CAT_232','CAT_233') 
         AND SHORT_CODE = '22222');

看看它是否有效。