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

Question

When this MySql query is executed:

update order_line_item oli
inner join orders o
set oli.active_orders = o.valid
where oli.order_id = o.id

I keep getting this error:

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

Any suggestions?

Additional Info:

Both "active_orders" and "valid" are INT
order_line_item can reference the same order.id for multiple lines

Table examples (currently)

table : order_line_item
id  order_id    active_orders
1   1   
2   2   
3   3   
4   4   
5   4   


table : orders
+-------+-------------+    
|   id  |   valid     |     
+-------+-------------+
|   1   |       1     |   
|   2   |       1     |   
|   3   |       0     |  
|   4   |       1     |  
|   5   |       1     |   
+-------+-------------+

With the query i'm trying to copy orders.valid and paste into orders_line_item.active_orders:

table : order_line_item
+-------+------------+-----------------+    
|   id  |   order_id |   active_orders |     
+-------+------------+-----------------+
|   1   |   1        |        1        |   
|   2   |   2        |        1        |   
|   3   |   3        |        0        |  
|   4   |   4        |        1        |  
|   5   |   4        |        1        |   
+-------+------------+-----------------+

table : orders
+-------+-------------+    
|   id  |   valid     |     
+-------+-------------+
|   1   |       1     |   
|   2   |       1     |   
|   3   |       0     |  
|   4   |       1     |  
|   5   |       1     |   
+-------+-------------+

Which of the columns in the query is declared to be DOUBLE? — Barmar
Welcome to Stack Overflow. You have not provided enough information for us to help you. Please read this note about asking good SQL questions. Then please edit your question. — O. Jones
@Barmar, that's the part that doesn't make sense to me. the value i'm trying to copy over to 'active_orders' is an integer value. No additional values are being joined. — Kosekid
Are you sure the error is coming from that query? I don't see any way it can produce that error. — Barmar
i'm a novice at this, so i just assumed i'm wrong. haha! I checked the data, don't see anything that would throw the query off. is there something else it could be? — Kosekid

dwir182 dwir182 · Accepted Answer · 2018-10-02T02:11:12

You are say :

Both "active_orders" and "valid" are INT

order_line_item can reference the same order.id for multiple lines

db<>fiddle Demo

See in my demo i am make all colum to INT and do your update statement

update order_line_item oli
inner join orders o
set oli.active_orders = o.valid
where oli.order_id = o.id

And no error show up. The error

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

Seems pretty clear there are DOUBLE. Do you really run this query and got this error? Or please show the desc your table.

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

1 Answers