CODEWITHSUNDEEP
mysqlsubquery
Rezuan
Rezuan

Reputation: 95

MySQL Subquery returns more than 1 row ERROR

I'm getting an error on my below mysql query. Please note that my subquery supposed to be returned 1627862 results. Is there any way to sort out this error with maintaining all the conditions of the subquery?

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

Upvotes: 0

Views: 916

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You could write this as an update join:

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

Upvotes: 2

jmsds
jmsds

Reputation: 225

The equal = in your MSISDN commpare expects a single value to be returned by your subquery, but the subquery is returning multiple rows. In order for your query to work, change = to ìnas below

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

See if it works.

Upvotes: 2

Related Questions