只想更新特定日期 a_msisdn 计数 > 100 的那些记录的状态:
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 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 );
但我得到了错误
ERROR 1242 (21000):子查询返回多于 1 行
1 个回答