多表更新时碰到的 ERROR 1292 (22007)隐式转换错误

表结构如下： 

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的功能