Question

[Solved] 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)
Enquirer: Emanuel

||

Solution #1:

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
Respondent: Darin Dimitrov

Solution #2:

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.

Respondent: Sidra

Solution #3:

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*

Respondent: halfpastfour.am

Solution #4:

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.

Respondent: codaddict

Solution #5:

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;
Respondent: Adrian Goia

Solution #6:

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.

Respondent: hrvoj3e

Solution #7:

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';
Respondent: Winnipass

Solution #8:

1292 – Truncated incorrect DOUBLE value:

This error occurs when you try to compare different types on SQL like `uniqueid` = 1610386969.1713 in this query:

UPDATE `cdr` SET `userfield`='survey=5,' WHERE `uniqueid` = 1610386969.1713

change it for passing the error on this UPDATE example:

UPDATE `cdr` SET `userfield`='survey=5,' WHERE `uniqueid` = '1610386969.1713'

But in your problem, if you change the AND to , the problem will be resolved

UPDATE shop_category SET name = 'Secolul XVI - XVIII', name_eng = '16th to 18th centuries' WHERE category_id = 4768
Respondent: Pejman Kheyri

Solution #9:

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.

Respondent: Joe Phillips

Solution #10:

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.

Respondent: Bytech

Solution #11:

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.

Respondent: thephper

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Most Popular

To Top
India and Pakistan’s steroid-soaked rhetoric over Kashmir will come back to haunt them both clenbuterol australia bossier man pleads guilty for leadership role in anabolic steriod distribution conspiracy