How to resolve MySQL Error Code: 1292. Truncated incorrect DOUBLE value?

Question

I'm trying to update 2 different tables with an update query as shown below

UPDATE  db1.table1 a, db2.table1 b 
SET       b.firstname  =  a.firstname,
          b.lastname   =  a.lastname,
          b.address    =  a.address,
          b.state      =  a.state,
          b.city       =  a.city,
          b.zip        =  a.zip             

WHERE a.stud_id=b.stud_id AND a.firstname IS NOT NULL AND b.firstname IS NULL
          AND str_to_date(a.joindate,'%m/%d/%Y') >= str_to_date('02/01/2012','%m/%d/%Y');

but when i tried to execute this query, MySQL kept throwing the following error

Error Code: 1292. Truncated incorrect DOUBLE value: 'CROUGH0000'

Though i've found much similar posts in stackoverflow, i couldn't find the exact solution to this problem.

Need some help. Thanks in advance

EDIT : Datatypes of each column are as follows

              b.firstname(varchar(25))  =  a.firstname(varchar(52)),
              b.lastname(varchar(25))   =  a.lastname(varchar(35)),
              b.address(varchar(40))    =  a.address(varchar(50)),
              b.state(char(2))      =  a.state(char(2)),
              b.city(varchar(25))       =  a.city(varchar(25)),
              b.zip(varchar(11))        =  a.zip(varchar(11))

Please add the data types for `stud_id` and `joindate` in each table. Also, please tell us where the value `'CROUGH0000'` comes from? Is that stud_id? — Bill Karwin, Oct 05 '14 at 17:21

D Mac · Answer 1 · 2013-04-05T21:13:27.320

2

If all those columns are varchar (as you've stated above) then the problem must be with a.stud_id=b.stud_id.

Check your data types in both tables a and b. Something has to be a DOUBLE or MySQL wouldn't be complaining about it.

edited Apr 05 '13 at 21:13

answered Apr 05 '13 at 19:04

D Mac

3,727
1
25
32

@D Mac a.stud_id and b.stud_id have the same datatypes – ben Apr 05 '13 at 19:16
Then something that you've stated above is wrong. In my experience, MySQL doesn't throw capricious error messages - there is something in there with a datatype of DOUBLE and that's where your problem is. Find it, and you'll know what to fix. – D Mac Apr 05 '13 at 19:37
@DMac Not necessarily, apparently. [Cf.](http://stackoverflow.com/questions/16068993/error-code-1292-truncated-incorrect-double-value-mysql#comment22935664_16068993) – WAF Jun 23 '15 at 14:52

score 2 · Answer 2 · edited Mar 02 '18 at 02:51

For me this error was showing because I was using 'and' to update 2 Queries instead of a 'comma'.

This snippet shows the error:

UPDATE employees 
SET 
lastname = 'Hill'and
email = 'mary.hill@classicmodelcars.com'
WHERE
employeeNumber = 1056;

This snippet shows the correction:

UPDATE employees 
SET 
lastname = 'Hill',
email = 'mary.hill@classicmodelcars.com'
WHERE
employeeNumber = 1056;

score 0 · Answer 3 · answered May 07 '19 at 12:20

I think that joindate column contains a String value that is not String-Date.

To check that, if possible, I propose to test following SQL command

UPDATE db1.table1 a
      ,db2.table1 b 
SET b.firstname = b.firstname,
WHERE a.stud_id=b.stud_id 
  AND a.firstname IS NOT NULL 
  AND b.firstname IS NULL;

If this work, nothing has been changed but you know that your issue is linked to joindate field or str_to_date function.

Caution: b.firstname is assigned to same value -> nothing is changed

score 0 · Answer 4 · answered Sep 13 '21 at 16:35

I had an unexpected issue where I was comparing data types properly in the where clause but still got the same exception.

I was having this exception just because I had a scheduled event on the table which I was trying to update that was clearly comparing different types of fields in the where clause. After fixing that issue everything worked properly.

How to resolve MySQL Error Code: 1292. Truncated incorrect DOUBLE value?

4 Answers4