MYSQL Truncated incorrect DOUBLE value

Question

When the SQL query below is executed   UPDATE shop category  SET name    Secolul XVI - XVIII       AND name eng    16th to 18th centuries   WHERE category id   4768   The following error is raised   1292 - Truncated incorrect DOUBLE value   Secolul XVI - XVIII    How to fix this     shop category table structure   category id   mediumint 8  name        varchar 250  name eng      varchar 250

User · Accepted Answer

You don t need the AND keyword  Here s the correct syntax of the UPDATE statement    UPDATE      shop category  SET      name    Secolul XVI - XVIII        name eng    16th to 18th centuries   WHERE      category id   4768

User · Answer

What it basically is  It s incorrect syntax that causes MySQL to think you re trying to do something with a column or parameter that has the incorrect type  DOUBLE    Learn from my mistake  In my case I updated the varchar column in a table setting NULL where the value 0 stood  My update query was like this   UPDATE myTable SET myValue   NULL WHERE myValue   0    Now  since the actual type of myValue is VARCHAR 255  this gives the warning    --------- ------ -----------------------------------------------    Level     Code   Message                                          --------- ------ -----------------------------------------------    Warning   1292   Truncated incorrect DOUBLE value   value xyz     --------- ------ -----------------------------------------------    And now myTable is practically empty  because myValue is now NULL for EVERY ROW in the table  How did this happen   internal screaming     Over 30k rows now have missing data   internal screaming intensifies     Thank goodness for backups  I was able to recover all the data   internal screaming intensity lowers   The corrected query is as follows   UPDATE myTable SET myValue   NULL WHERE myValue    0                                                                                                           Quotation here    I wish this was more than just a warning so it s less dangerous to forget those quotes    End internal screaming

User · Answer

I was getting this exception not because of AND instead of comma  in fact I was having this exception just because I was not using apostrophes in where clause   Like my query was  update table set coulmn1  something  where column2 in  00012121     when I changed where clause to  where column2 in   00012121    then the query worked fine for me

User · Answer

Mainly invalid query strings will give this warning   Wrong due to a subtle syntax error  misplaced right parenthesis  when using INSTR function   INSERT INTO users  user name  SELECT name FROM site users WHERE INSTR status   active  gt 0     Correct   INSERT INTO users  user name  SELECT name FROM site users WHERE INSTR status   active   gt 0

User · Answer

I experienced this error when using bindParam  and specifying PDO  PARAM INT where I was actually passing a string  Changing to PDO  PARAM STR fixed the error

User · Answer

Try replacing the AND with    UPDATE shop category  SET name    Secolul XVI - XVIII   name eng    16th to 18th centuries   WHERE category id   4768   The UPDATE Syntax shows comma should be used as the separator

User · Answer

If you re getting this problem with an insert that looks like the one below  the problem may simply be the lack of a space between -- and the comment text   insert into myTable  a  b  c  values      123 --something    345 --something else    567 --something something else      The problem with this is that the --something should actually be -- something with a space

User · Answer

I just wasted my time on this and wanted to add an additional case where this error presents itself   SQL Error  1292   Truncated incorrect DOUBLE value   N0003    Test data  CREATE TABLE  table1          value1  VARCHAR 50  NOT NULL     INSERT INTO table1  value1  VALUES   N0003     CREATE TABLE  table2          value2  VARCHAR 50  NOT NULL      INSERT INTO table2  value2  SELECT value1 FROM table1 WHERE 1 ORDER BY value1 0   The problem is ORDER BY value1 0 - type casting   I know that it does not answer the question but this is the first result on Google for this error and it should have other examples where this error presents itself

User · Answer

It seems mysql handles the type casting gracefully with SELECT statements  The shop id field is of type varchar but the select statements works  select   from shops where shop id   26244317283    But when you try updating the fields  update stores set store url    https   test-url com  where shop id   26244317283    It fails with error Truncated incorrect DOUBLE value   1t5hxq9   You need to put the shop id 26244317283 in quotes  26244317283  for the query to work since the field is of type varchar not int  update stores set store url    https   test-url com  where shop id    26244317283

User · Answer

I did experience this error when I tried doing an WHERE EXIST where the subquery matched 2 columns that accidentially was different types  The two tables was also different storage engines   One column was a CHAR  90  and the other was a BIGINT  20    One table was InnoDB and the other was MEMORY   Part of query         AND EXISTS  select objectid from temp objectids where temp objectids objectid   items raw objectid      Changing the column type on the one column from BIGINT to CHAR solved the issue

[mysql] MYSQL Truncated incorrect DOUBLE value

Examples related to mysql