表结构如下：
Create Table: CREATE TABLE `test_t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_no` varchar(20) DEFAULT NULL,
`app_id` varchar(20) DEFAULT NULL,
`access_resource` varchar(20) DEFAULT NULL,
`status` varchar(10) DEFAULT NULL,
`ager` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
语句如下：
UPDATE test_t2 t1 ,(
SELECT s.`customer_no`, s.`app_id`, s.`access_resource`, s.`status`, MAX(id) mxid
FROM test_t2 s
WHERE s.`status` = 0
GROUP BY s.`customer_no`, s.`app_id`, s.`access_resource`, s.`status`
HAVING COUNT(*) > 1
) t2
SET t1.`status` = 1
where t1.`customer_no` = t2.customer_no
AND t1.`app_id` = t2.app_id
AND t1.`access_resource` = t2.access_resource
AND t1.`status` = t2.status
and t1.`id` <> t2.mxid;
执行后，报错：
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
原来test_t2表的status字段是varchar（10）
当set t1.`status` = 1时，将‘a’转化为DOUBLE value处理，隐式转换失败，故报错。
解决方式：
修改测试表结构
update test_t2 set status='0' where status!='0';
alter table test_t2 modify status tinyint;
再次执行：
Query OK, 10 rows affected (0.29 sec)
Rows matched: 10 Changed: 10 Warnings: 0
再测试另外一种多表更新：
UPDATE test_t2 t1
INNER JOIN (
SELECT s.`customer_no`, s.`app_id`, s.`access_resource`, s.`status`, MAX(id) mxid
FROM test_t2 s
WHERE s.`status` = 0
GROUP BY s.`customer_no`, s.`app_id`, s.`access_resource`, s.`status`
HAVING COUNT(*) > 1
) t2 ON t1.`customer_no` = t2.customer_no
AND t1.`app_id` = t2.app_id
AND t1.`access_resource` = t2.access_resource
AND t1.`status` = t2.status
SET t1.`status` = 1
WHERE t1.`id` <> t2.mxid Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
这两句update语句都是实现了：将符合条件的记录的status置为1的功能