Truncated incorrect integer value MySQL

Question

I have a table ABC that has many columns, two of which are: ID - VARCHAR(10) and ROLE VARCHAR(10). Now I have been trying to update the column ROLE using the ID and this is the query:

UPDATE TABLE ABC
SET    ROLE='READ_ONLY'
WHERE  ID='AB234PQR'

Now for some unknown reason, i have been getting the error - truncated incorrect integer value. I have no idea where I am going wrong.I have been banging my head over this for a while now.

I have visited other questions with the similar title.All use convert or some other function in where clause, But I have not used any such thing, still it gives me the same error.

I checked the table description and it seems fine. Where can I be going wrong? Any help is appreciated.

@P.Salmon I think that could be the issue. I did create some triggers recently.I will have a look at them and get back to you. Thanks, mate. — Allan Fernandes, Mar 17 '18 at 20:08
@P.Salmon You were right!! There were triggers on that table creating the issue!! Thanks a ton!! — Allan Fernandes, Mar 20 '18 at 14:20

score 0 · Answer 1 · answered Mar 17 '18 at 18:39

0

Can you please try this:

UPDATE ABC
SET    ROLE='READ_ONLY'
WHERE  ID='AB234PQR'

answered Mar 17 '18 at 18:39

Manash Kumar

995
6
13

score 0 · Answer 2 · answered Mar 17 '18 at 19:12

0

The correct syntax to update table entries is:

UPDATE `table_name` 
SET `column_name` = 'value'
WHERE `column_name2` = 'value2';

It is as well recommended to use backticks around table names and column names, just like the way you can see in my snippet above.

Therefore using

UPDATE `ABC`
SET    `ROLE` = 'READ_ONLY'
WHERE  `ID` = 'AB234PQR'

should do the trick.

answered Mar 17 '18 at 19:12

webFashion

748
9
17

I will give this a try. Thanks. – Allan Fernandes Mar 17 '18 at 20:09

Truncated incorrect integer value MySQL

2 Answers2