首页 文章

MySQL子查询返回多行ERROR

提问于
浏览
0

我在下面的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):子查询返回超过1行

2 回答

  • 2

    您可以将其写为更新连接:

    UPDATE SUBSCRIPTION_LOG sl
    INNER JOIN SUBSCRIPTION s
        ON sl.MSISDN = s.MSIDSN
    SET sl.SUBSCRIPTION_STATUS = 'D',
        sl.DEACTIVATION_DATE = NOW(),
        sl.DEACTIVATION_CHANNEL = 'SYSTEM'
    WHERE
        sl.SUBSCRIPTION_STATUS = 'A' AND
        sl.SHORT_CODE = '22222' AND
        DATEDIFF(NOW(), s.ACTIVATION_DATE) > s.LAST_CHARGED_VALIDITY AND
        s.OFFER_CODE NOT IN ('CAT_228','CAT_229','CAT_232','CAT_233') AND
        s.SHORT_CODE = '22222';
    
  • 2

    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');
    

    看看它是否有效 .

相关问题