-1

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:

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

  3. 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     |   
+-------+-------------+

  4. 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     |   
+-------+-------------+
Barmar
  • 741,623
  • 53
  • 500
  • 612
Kosekid
  • 1
  • 1
  • 2
    Which of the columns in the query is declared to be `DOUBLE`? – Barmar Oct 01 '18 at 22:03
  • Welcome to Stack Overflow. You have not provided enough information for us to help you. Please [read this note about asking good SQL questions](http://meta.stackoverflow.com/a/271056/). Then please [edit] your question. – O. Jones Oct 01 '18 at 22:18
  • @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 Oct 01 '18 at 22:42
  • Are you sure the error is coming from that query? I don't see any way it can produce that error. – Barmar Oct 01 '18 at 23:44
  • 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 Oct 02 '18 at 00:22
  • See [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c55fc1f7ff10205ece6b2552a7901164) i am follow your table and do like your and nothing error show up.. Desc your table – dwir182 Oct 02 '18 at 00:45

1 Answers1

0

You are say :

  1. Both "active_orders" and "valid" are INT
  2. 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.

dwir182
  • 1,539
  • 10
  • 20
  • Yes, this is the only error that I am seeing. From what you all are saying it sounds like a problem with my data? I checked for anomalies and didn't find anything. I have no idea what else it could be. – Kosekid Oct 02 '18 at 15:33
  • It can be the data. So value `6893Order` who have this value? which column? `valid` column? – dwir182 Oct 03 '18 at 00:05