#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

Your SQL is not valid. You end the statement with `;` and after that use `SET` to change some of the columns? That's not any syntax I recognize. Also please share the output of `SHOW CREATE TABLE wooderso_8K7YhUihGr9s4k8xE1w9.gsti1_wpcrm_adjustment;` so we can see what the data types for your columns are. Also `SELECT @@version;` so we know what version and fork of MySQL you are using. — Bill Karwin, Dec 22 '20 at 23:03
Table Create Table gsti1_wpcrm_adjustment CREATE TABLE `gsti1_wpcrm_adjustment` ( `adjustm... — Eoin, Dec 22 '20 at 23:07
comma spaprated tables, are very old fashiond and should be changed to common `join` Besides your set statement doesn't makes sense i thing it should be a AND — nbk, Dec 22 '20 at 23:07
Thanks @nbk I tried changing commas to `AND` and I added a `JOIN` but I still get a truncted error. ` #1265 - Data truncated for column 'quantity' at row 5` — Eoin, Dec 22 '20 at 23:11
Use the SHOW CREATE TABLE statement, and copy *the whole output* not just the first line. Then use the `edit` link above to open your original question. Don't try to paste the whole create table into a comment. — Bill Karwin, Dec 22 '20 at 23:14
make in workbe3nch or what gui you use a SHOW CREATE TABLE tbl_Master_Data are you sure you are using mysql — nbk, Dec 22 '20 at 23:14
I'm using PHPMyAdmin and i'm sure I'm using MySql. `Server version: 5.7.32 - MySQL Community Server (GPL) ` — Eoin, Dec 22 '20 at 23:16
the truncated message appears when the datatpyes don'i match lile s.CUST with qiatitity as we don't know the structure anbd datatypües of your columns we can help not that muchsage appears when the datatypes don't match up s.CUST with quantity, — nbk, Dec 22 '20 at 23:17
I have updated the question now, is that the information that is required? — Eoin, Dec 22 '20 at 23:19
Ah I see I am mixing a pallet_id which is int with a PalletNo which is a varchar. I must find the correct pallet_id from another table first I think. Thank you, that has helped. — Eoin, Dec 22 '20 at 23:21
Take a look at your insert query and the column you define inside. You said you got `#1265 - Data truncated for column 'quantity' at row 5` message. Its because you're inserting a `VARCHAR` value (`s.Cust`) into an `INT` datatype column (`quantity`). — FanoFN, Dec 23 '20 at 00:54
I'm not @tcadidot0 I'm setting a `decimal` `s.Issue` as an `int` which still won't work. But I am setting `PalletNo` which is a `varchar` into `pallet_id` which is an `int`. I've clearly mixed up my record ID's. But you guys have helped me to see the error of my ways and now, hopefully, I can progress. — Eoin, Dec 23 '20 at 10:08
It anyone wants the delicious points please answer the question or else I will fill in the answer after a few days if one of you guys doesn't. — Eoin, Dec 23 '20 at 10:09
Also if anyone knows why `SHOW CREATE TABLE` only showed the first line instead of what I needed that would be nice to understand. I used `describe` instead to get the answer. — Eoin, Dec 23 '20 at 10:09
Actually, I'm referring to your `INSERT` query.. not the `SET` query. From what I know, the message is coming from `INSERT` query. About the `SHOW CREATE TABLE` in phpMyAdmin, after you run it, click the "+ Option" link on top of the query result, select 'Full texts' then click 'Go'. It should show the full create table syntax. — FanoFN, Dec 24 '20 at 01:27
Hmm, ok perhaps I am getting things wrong then because I am trying to insert into and also set. But I think I am misunderstanding what I am doing. How do I know what data is being inserted into which column? — Eoin, Dec 24 '20 at 11:01

FanoFN · Accepted Answer · 2020-12-28T04:27:09.717

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.

Man that is such a fantastic explanation and REALLY helps me a LOT. Thanks so much for taking the time to write it up in that detail, you clearly picked up that I did not understand fully the process I was following. I am eternally in your gratitude. — Eoin, Dec 29 '20 at 15:10
Is there an easy way to format the tables the way you did? Or is it just a case of hitting space the right amount of times? — Eoin, Dec 29 '20 at 15:11
Glad I could help. Actually there are a bunch of website that can generate the table format but the one I usually use is https://ozh.github.io/ascii-tables/ — FanoFN, Dec 30 '20 at 00:33
Given the issue is the same as quantity and it can never be a decimal, does that mean I can do a query to get this migrated, or does this mean it will keep refusing it because it's a mismatch. And if I need to do it, is there a way around the mismatch? Or is the best way just to move it to a new table then manually change it to an int? — Eoin, Dec 30 '20 at 21:22
AFAIK, in this case, it will refuse to insert only if the data contain incorrect character corresponding datatype for the column and with `INSERT` BUT if you do `INSERT IGNORE`, the operation is still successful however the inserted data only contain characters that correspond to the datatype. For this _"Given the issue is the same as quantity and it can never be a decimal, does that mean I can do a query to get this migrated"_ , yes, this won't refuse even if the original data contain decimal value. https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b16d064b9aff6d69e0ca2bb36faf2f3a — FanoFN, Dec 31 '20 at 00:53

#1292 - Truncated incorrect DOUBLE value:

1 Answers1