mysql error 1292 when using cast in update statement

Question

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).

show table structure please – Noam Rathaus Jan 15 '14 at 16:05 — Noam Rathaus, Jan 15 '14 at 16:05
The data type of emp is varchar(7) – Chine Jan 15 '14 at 16:07 — Chine, Jan 15 '14 at 16:07

score 5 · Answer 1 · answered Apr 04 '17 at 22:33

5

Try this

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

answered Apr 04 '17 at 22:33

Anton

5,831
3
35
45

score 2 · Accepted Answer · edited Nov 29 '17 at 05:20

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).

score -1 · Answer 3 · answered Jan 15 '14 at 16:33

It's easy to reproduce, e.g.:

CREATE TABLE new2006 (
 emp VARCHAR(7)
);
INSERT INTO new2006 (emp) VALUES ('95.00');
UPDATE new2006 SET emp=CAST(emp AS UNSIGNED);

I suspect the problem is related to internal conversions from/to base 2 because there's no error message when you cast to DECIMAL:

UPDATE new2006 SET emp=CAST(emp AS DECIMAL);

... or when you remove the decimals from source data:

INSERT INTO new2006 (emp) VALUES ('95');

I couldn't find anything in the manual page for CAST() that sheds light on the subject. My guess is that MySQL chooses different algorithm or internal variable types depending on the input scenario and sometimes truncation happens while performing internal calculations.

mysql error 1292 when using cast in update statement

3 Answers3

Linked