【工作遇到】update为什么没有更新

今天在执行SQL的时候，脑子转不过玩来了

update email_stat_base_send_20120416 
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;

commit;

select  SEND_STATUS ,SEND_RESULT,BACK_TIME from email_stat_base_send_20120416 
 where SEND_STATUS='4'  and ACTIVITY_ID=9671;

 查询结果中为什么SEND_RESULT有-1 有0，有1 ，没有成功改成-1呢？
这是因为表中原来有SEND_STATUS='4'的SEND_RESULT是别的值， 而我们更新的只是SEND_STATUS='0' and ACTIVITY_ID=9671;的记录
 select ...where SEND_STATUS='4'  and ACTIVITY_ID=9671;选择的范围较大，包括了原来SEND_STATUS是4的记录和刚刚由0改为4的记录。

 所以看到查询结果中 SEND_RESULT并不全部是-1！！！！

举例说明。

 CREATE TABLE `testbobo` (
  `ITEM_SEND_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `MESSAGE_NAME` varchar(100) DEFAULT NULL,
  `MESSAGE_ID` varchar(100) DEFAULT NULL,
  `ACTIVITY_ID` bigint(20) NOT NULL,
  `COM_ID` bigint(20) NOT NULL,
  `LINKMAN_EMAIL` varchar(100) NOT NULL,
  `EMAIL_DOMAIN` varchar(100) NOT NULL,
  `SEND_RESULT` int(11) DEFAULT NULL,
  `SEND_STATUS` char(1) NOT NULL COMMENT '默认为0 0：未发送，1：发送成功，2：发送终止，3：硬退，4：软退',
  `BACK_TIME` datetime DEFAULT NULL,
  `ADD_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '用户第一次添加信息的时间',
  `SEND_TIME` datetime DEFAULT NULL,
  `LINKMAN_ID` bigint(20) NOT NULL,
  `UPDATE_COUNT` int(11) DEFAULT NULL,
  PRIMARY KEY (`ITEM_SEND_ID`),
  KEY `index_addtime` (`ADD_TIME`) USING BTREE,
  KEY `idx_EMAIL_STAT_BASE_SEND_1` (`LINKMAN_ID`,`ACTIVITY_ID`,`COM_ID`),
  KEY `idx_testbobo _1` (`COM_ID`,`ACTIVITY_ID`,`ADD_TIME`),
  KEY `idx_testbobo _3` (`ACTIVITY_ID`,`SEND_STATUS`,`ADD_TIME`),
  KEY `idx_testbobo_2` (`MESSAGE_ID`,`ACTIVITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=176580756 DEFAULT CHARSET=utf8

insert into testbobo  (SEND_STATUS, ACTIVITY_ID,SEND_RESULT)   values (0,9671,5);
insert into testbobo    (SEND_STATUS, ACTIVITY_ID,SEND_RESULT)   values (4,9671,0);
insert into testbobo     (SEND_STATUS, ACTIVITY_ID,SEND_RESULT)   values (4,9671,0);
insert into testbobo     (SEND_STATUS, ACTIVITY_ID,SEND_RESULT)   values (4,9671,0);
insert into testbobo      (SEND_STATUS, ACTIVITY_ID,SEND_RESULT)   values (4,9671,0);

mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo ;
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4           |        9671 |          -1 | 
| 4           |        9671 |           0 | 
| 0           |        9671 |           5 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
+-------------+-------------+-------------+
7 rows in set (0.00 sec)

update testbobo 
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;

mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo 
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4           |        9671 |          -1 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |          -1 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
| 4           |        9671 |           0 | 
+-------------+-------------+-------------+
7 rows in set (0.00 sec)

看到，满足条件where SEND_STATUS='0' and ACTIVITY_ID=9671;的成功更新了（SEND_RESULT由5改成了-1）
其他 SEND_STATUS='4‘的没有更新（SEND_RESULT 还是0没有改成-1）。因为不满足条件