‘'Truncated不正确整数值’错误

Question

在尝试运行以下查询时，会收到Truncated incorrect INTEGER value: 'D'警告：

UPDATE MESSAGES
SET status = CASE
    WHEN 
    (from_id='111111111' AND status='A') THEN 'S'
    WHEN 
    (to_id  ='111111111' AND status='A') THEN 'R'
    WHEN 
    ((from_id  ='111111111' AND status='R') OR
    (to_id  ='111111111' AND status='S')) THEN 'D'
    END
WHERE primary_key = '236499681204'
AND
    (CASE
    WHEN 
    (from_id='111111111' AND status='A') THEN 'S'
    WHEN 
    (to_id  ='111111111' AND status='A') THEN 'R'
    WHEN 
    ((from_id  ='111111111' AND status='R') OR
    (to_id  ='111111111' AND status='S')) THEN 'D'
    END) is not null

我读过MySQL 'Truncated incorrect INTEGER value'和MYSQL Truncated incorrect INTEGER value error的帖子。但它们不适用于我的案子。在这里，status是VARCHAR(1)类型。

我遗漏了什么吗？

编辑

下面是创建表的查询：

CREATE TABLE  `MESSAGES` (
  `primary_key` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `from_id` varchar(9) NOT NULL,
  `to_id` varchar(9) NOT NULL,
  `status` varchar(1) NOT NULL,
  PRIMARY KEY (`primary_key`)
) ENGINE=MyISAM AUTO_INCREMENT=123456789876 DEFAULT CHARSET=latin1

请帮助:(

Answer 1

这不是因为status，而是因为比较

(CASE
    WHEN 
    (from_id='111111111' AND status='A') THEN 'S'
    WHEN 
    (to_id  ='111111111' AND status='A') THEN 'R'
    WHEN 
    ((from_id  ='111111111' AND status='R') OR
    (to_id  ='111111111' AND status='S')) THEN 'D'
END) is not null

为了避免错误，请将CASE的结果转换为如下所示的CHAR：

CAST( (CASE
    WHEN 
    (from_id='111111111' AND status='A') THEN 'S'
    WHEN 
    (to_id  ='111111111' AND status='A') THEN 'R'
    WHEN 
    ((from_id  ='111111111' AND status='R') OR
    (to_id  ='111111111' AND status='S')) THEN 'D'
END) AS CHAR) is not null

现在它应该工作了，ok :)