#1292 - Truncated incorrect DOUBLE value:

Question

I promise I have read most of the internet, but can't find my answer. Things I have read so far. 1292 Truncated incorrect DOUBLE value: ''

1292 Truncated incorrect DOUBLE value: ''

Error Code: 1292 - Truncated Incorrect DOUBLE value: '-'

1292 Truncated incorrect DOUBLE value

Invalid datetime format: 1292 Truncated incorrect DOUBLE value Error Code: 1292. Truncated incorrect DOUBLE value: 'yes'

Warning: #1292 Truncated incorrect DOUBLE value: 'Motor Bike'

MEANING OF ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'JAIN'

Error Number: 1292 Truncated incorrect DOUBLE value in my SQL error

SQL Error (1292): Truncated incorrect DOUBLE value: '6893Order '

MySQL: #1292 - Truncated incorrect DOUBLE value: '...'

All of these problems vary so much for the error : #1292 - Truncated incorrect DOUBLE value: 'CPL772-00'

Here is my query

insert into `wooderso_8K7YhUihGr9s4k8xE1w9`.`gsti1_wpcrm_adjustment`
(
    pallet_id,
    quantity,
    message,
    created_date,
    modified_date
)
select
s.TFR,
s.Cust,
s.Issue,
s.PalNo,
Date(s.Date)

from
`wooderso_wsms`.`dbo.tbl_Master_Data` s, `wooderso_dev`.`gsti1_wpcrm_adjustment` n
where
s.`TFR` = 1;
SET n.message = s.Cust, n.modified_date = s.Date, n.created_date = s.Date,  n.pallet_id = s.PalNo, n.quantity = s.Issue;

Please lord someone help me out of my misery. The answers above are all totally different problems with the same error, it's like finding a needle in a haystack.

Updated Query:

insert into `wooderso_dev`.`gsti1_wpcrm_adjustment`
(
    pallet_id,
    quantity,
    message,
    created_date,
    modified_date
)
select
s.TFR,
s.Cust,
s.Issue,
s.PalNo,
Date(s.Date)

from
`wooderso_wsms`.`dbo.tbl_Master_Data` s JOIN `wooderso_dev`.`gsti1_wpcrm_adjustment` n
where
s.`TFR` = 1;
SET n.message = s.Cust AND 
n.modified_date = s.Date AND 
n.created_date = s.Date AND
n.pallet_id = s.PalNo AND
n.quantity = s.Issue;

Scheme show create table wooderso_8K7YhUihGr9s4k8xE1w9.gsti1_wpcrm_adjustment; gives gsti1_wpcrm_adjustment CREATE TABLE gsti1_wpcrm_adjustment(adjustm...`

So I tried

describe `wooderso_8K7YhUihGr9s4k8xE1w9`.`gsti1_wpcrm_adjustment` 

Field   Type    Null    Key     Default     Extra   
adjustment_id   int(11) unsigned    NO  PRI     NULL    auto_increment
pallet_id   int(11) unsigned    NO  MUL     NULL    
quantity    int(10)     NO      NULL    
modified_date   datetime    NO      NULL    
created_date    datetime    NO      NULL    
message     text    NO      NULL 

describe `wooderso_wsms`.`dbo.tbl_Master_Data`;
Field   Type    Null    Key     Default     Extra   
RecID   mediumint(9)    YES         NULL    
PalNo   varchar(15)     YES         NULL    
Prod    varchar(50)     YES         NULL    
SpecNo  varchar(11)     YES         NULL    
ProdCode    varchar(17)     YES         NULL    
CostPrice   decimal(8,4)    YES         NULL    
SellPrice   decimal(9,4)    YES         NULL    
Weight  decimal(22,17)  YES         NULL    
Rcvd    mediumint(9)    YES         NULL    
Issue   decimal(6,1)    YES         NULL    
Bal     decimal(7,1)    YES         NULL    
Date    varchar(19)     YES         NULL    
Cust    varchar(221)    YES         NULL    
OrRefNum    varchar(50)     YES         NULL    
Purch   varchar(11)     YES         NULL    
TFR     tinyint(4)  YES         NULL    
ProdQual    tinyint(4)  YES         NULL    
Note    varchar(221)    YES         NULL

FanoFN · Accepted Answer

At closer look, I found that there are a lot of mismatch between your destination table columns vs source table columns. Look at you INSERT INTO tablename (column1, column2 ...) . The column order that you define in your bracket correspond to the column order in your insert value; either SELECT or VALUES. In your case, it's SELECT. So, to illustrate, your INSERT query is actually like this:

INSERT INTO mytable (pallet_id, quantity, message, created_date, modified_date)
             SELECT    s.TFR,     s.Cust,  s.Issue,  s.PalNo,      Date(s.Date) ...

Look at the each column you define in INSERT INTO correspond to the columns you define in SELECT, that's how the INSERT order work. So basically:

INSERT INTO mytable (| pallet_id | quantity | message | created_date | modified_date |)
                         ︾︾        ︾︾       ︾︾         ︾︾            ︾︾
             SELECT  |  s.TFR    |  s.Cust  | s.Issue |   s.PalNo    | Date(s.Date)  |

Lets look at the datatype:

| table to INSERT to     | source table              |
+------------------------+---------------------------+
| pallet_id INT(11)      |  s.TFR TINYINT(4)         | < ok: both are integer datatype
| quantity INT(10)       |  s.Cust VARCHAR(221)      | < mismatch 
| message TEXT           |  s.Issue DECIMAL(6,1)     | < mismatch 
| created_date DATETIME  |  s.PalNo VARCHAR(15)      | < mismatch 
| modified_date DATETIME |  Date(s.Date) VARCHAR(19) | < mismatch

I have run tests on a fiddle here https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5abf830b2993c9b6ff99df81ad504e5e and the only way you can make it work is by doing INSERT IGNORE. However, since most of the column datatype doesn't match, I strongly suggest you fix that part first. The reason is, with mismatched datatype, you'll almost certainly insert wrong information.

I take that the SET syntax is the actual operation you really want to happen but the invalid SET syntax also have a lot of mismatch.:

SET n.message = s.Cust, n.modified_date = s.Date, n.created_date = s.Date,  
    n.pallet_id = s.PalNo, n.quantity = s.Issue;

| column to SET value      | column value source       |
+--------------------------+---------------------------+
| n.message TEXT           |  s.Cust VARCHAR(221)      | < ok
| n.modified_date DATETIME |  s.Date VARCHAR(19)       | < mismatch
| n.created_date DATETIME  |  s.Date VARCHAR(19)       | < mismatch
| n.pallet_id INT(11)      |  s.PalNo VARCHAR(15)      | < mismatch 
| n.quantity INT(10)       |  s.Issue DECIMAL(6,1)     | < mismatch

So let's say, for example we change the INSERT query to something like this:

INSERT INTO mytable (message, modified_date, created_date, pallet_id, quantity)
             SELECT   s.Cust, s.Date, s.Date,  s.PalNo, s.Issue

I can say that even if you make sure that all the values in s.Date follow the same date format standard and s.Palno only have numbers (both to make them match with the destination column), but n.quantity have a very high possibility of wrong value inserted since the source value is DECIMAL datatype. That means if the original value is 10.6 once you do the INSERT to n.quantity, the decimal value will be missing.

#1292 - Truncated incorrect DOUBLE value:

Answers (1)

Related Questions