[mysql] MYSQL Truncated incorrect DOUBLE value

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)

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';

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'>0);

Correct:

INSERT INTO users (user_name) SELECT name FROM site_users WHERE
INSTR(status, 'active')>0;

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.

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.

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*}

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.

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.

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.

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.