Update query over nested select query

Question

Want to update status of only those record whose a_msisdn count > 100 on specific date:

mysql> select * from cc_info limit 1;
+----------------------+--------------+--------+------------+-------------+--------------+--------+------------+-------------+---------------------+---------------------+--------------+--------+---------------+
| transaction_id       | a_msisdn     | a_imsi | a_sub_type | a_lang_code | b_msisdn     | b_imsi | b_sub_type | b_lang_code | incoming_timestamp  | process_timestamp   | request_mode | status | retry_counter |
+----------------------+--------------+--------+------------+-------------+--------------+--------+------------+-------------+---------------------+---------------------+--------------+--------+---------------+
| -9223371087585181184 | 923345070688 |        |          0 |           0 | 923333340955 |        |          1 |           0 | 2019-08-04 15:58:42 | 2019-08-04 15:58:49 |            0 |      3 |             0 |

UPDATE (moved from comments)

update cc_info 
set status =4 
where a_msisdn = ( select a_msisdn 
                   from cc_info 
                   where DATE(incoming_timestamp) ='2019-08-04' 
                     AND status = 3 
                   group by a_msisdn 
                   having count(*) > 100 );

But I am getting the error

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

update cc_info set status =4 where a_msisdn = ( select a_msisdn from cc_info where DATE(incoming_timestamp) ='2019-08-04' AND status = 3 group by a_msisdn having count(*) > 100 ); — Faizan Naseem, Aug 07 '19 at 08:23
But I am getting the error
ERROR 1242 (21000): Subquery returns more than 1 row — Faizan Naseem, Aug 07 '19 at 08:23

score 0 · Accepted Answer · answered Aug 07 '19 at 08:34

0

update cc_info dst, ( select a_msisdn 
                      from cc_info 
                      where DATE(incoming_timestamp) ='2019-08-04' 
                        AND status = 3 
                      group by a_msisdn 
                      having count(*) > 100 ) src
set dst.status = 4 
where dst.a_msisdn = src.a_msisdn

answered Aug 07 '19 at 08:34

Akina

19,866
2
17
21

! How can we optimize the above query if record in table cc_info almost 4 million???? – Faizan Naseem Aug 07 '19 at 11:28
CREATE INDEX idx_name ON cc_info (status, incoming_timestamp, a_msisdn) (or the same, but with the first two fields swapped) may help. – Akina Aug 07 '19 at 11:41
Starting from 8.0.13 you may create more effective index: CREATE INDEX idx_name ON cc_info (status, (DATE(incoming_timestamp)), a_msisdn), but your version cannot... – Akina Aug 07 '19 at 11:46

Update query over nested select query

1 Answers1