Error Code 1292 - Truncated incorrect DOUBLE value - Mysql
TL; DR
This might also be caused by applying OR
to string columns / literals.
Full version
I got the same error message for a simple INSERT
statement involving a view:
insert into t1 select * from v1
although all the source and target columns were of type VARCHAR
. After some debugging, I found the root cause; the view contained this fragment:
string_col1 OR '_' OR string_col2 OR '_' OR string_col3
which presumably was the result of an automatic conversion of the following snippet from Oracle:
string_col1 || '_' || string_col2 || '_' || string_col3
(||
is string concatenation in Oracle). The solution was to use
concat(string_col1, '_', string_col2, '_', string_col3)
instead.
This message means you're trying to compare a number and a string in a WHERE
or ON
clause. In your query, the only potential place where that could be occurring is ON ac.company_code = ta.company_code
; either make sure they have similar declarations, or use an explicit CAST
to convert the number to a string.
If you turn off strict
mode, the error should turn into a warning.
I was facing the same issue. Trying to compare a varchar(100) column with numeric 1. Resulted in the 1292 error. Fixed by adding single quotes around 1 ('1').
Thanks for the explanation above
I corrected this error as there was a syntax error or some unwanted characters in the query, but MySQL was not able to catch it. I was using and
in between multiple fields during update, e.g.
update user
set token='lamblala',
accessverion='dummy' and
key='somekey'
where user = 'myself'
The problem in above query can be resolved by replacing and
with comma(,
)