'Truncated incorrect INTEGER value' Error

Question

I'm getting Truncated incorrect INTEGER value: 'D' warning while I try to run the following query:

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

I have read the posts MySQL 'Truncated incorrect INTEGER value' and MYSQL Truncated incorrect INTEGER value error. But they dont apply to my case. Here, status is of type VARCHAR(1).

Is there something I'm missing?

Edit

Here's the query for creating the table:

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

Please help :(

@juergend Please look at my edit and help. I'm kinda stuck. cant find anything elsewhere — th3an0maly, Oct 04 '12 at 17:27
Doesn't appear that you are comparing the case statements to anything after your `AND` ... '`D IS NOT NULL`' makes no sense. — Kermit, Oct 04 '12 at 17:27
`'236499681204'` is a character literal. Do **not** use it to compare numbers. Numbers do **not** need quotes. Never rely on implicit data conversion - use proper literals (only use single quotes for character values). — , Oct 04 '12 at 17:28
I'm using a `NamedParameterJDBCQuery` and need to pass that. I cant store such a value in `int`. So I store it as a `String` :( Is there a better way? Besides, the error clearly says `incorrect INTEGER value: 'D'` and that means it's something wrong with `status`. — th3an0maly, Oct 04 '12 at 17:31

score 4 · Accepted Answer · answered Oct 05 '12 at 07:04

it is not because of status but because of the comparison

(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

to avoid the error, cast the result of CASE into a CHAR like this:

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

now it should work ok :)

'Truncated incorrect INTEGER value' Error

1 Answers1