[SOLVED] mysql error 1292 when using cast in update statement

mysql error 1292 when using cast in update statement

The below statement returns "Error Code: 1292. Truncated incorrect INTEGER value: '95.00' 1.132 sec "

update new2006 set new2006.emp=cast(emp as unsigned) where IsNum(emp)=0;

But if I use the below statement, I can get result 95 successfully.

select cast(emp as unsigned) from new2006 where IsNum(emp)=0;

PS:The data type of emp is varchar(7).

Solution

You are in a strict SQL mode; as documented (emphasis added):

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For statements that do not change data, such as SELECT, invalid values generate a warning in strict mode, not an error.

If you want the UPDATE to succeed without error, you will need to change to a non-strict SQL mode, or else first manipulate the string into a value that won't throw an error, e.g. SUBSTRING_INDEX(emp, '.', 1).