Mysql Truncated Incorrect Double Value

MYSQL Truncated incorrect DOUBLE value

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

Error Code 1292 - Truncated incorrect DOUBLE value - Mysql

This message means you're trying to compare a number and a string in a WHERE or ON clause. In your query, the only potential place where that could be occurring is ON ac.company_code = ta.company_code; either make sure they have similar declarations, or use an explicit CAST to convert the number to a string.

If you turn off strict mode, the error should turn into a warning.

#1292 - Truncated incorrect DOUBLE value:

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.

SQL - Truncated incorrect DOUBLE value

+ is numeric addition, not string concatenation... so MySQL tries to cast your values to numbers. Having no better plan, it goes for the fallback type of DOUBLE (64 bit floating point value). But since your strings don't make very convincing numbers, you get warnings.

The correct construct for string concatenation:

ON t1.titleid = CONCAT(t2.name, '_00')

Truncated incorrect DOUBLE value using DELETE statement

You can't bind strings to actual column names in a prepared statement. So, the attribute column names must be hard-coded. One pattern which might work would be:

String sql = "";
if ("COL1".equals(attribute)) {
    sql = "DELETE FROM course WHERE COL1 = ?";
}
else if ("COL2".equals(attribute)) {
    sql = "DELETE FROM course WHERE COL2 = ?";
}
else {
    sql = "DELETE FROM course WHERE COL3 = ?";
}
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, d1);
boolean rows = statement.execute();

---

Related Topics