• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL update issue

 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.

I want to update the total amount in the table "order" calculated from the SUM of ordelines in the "orderline" table. But I get an error:

Error Code : 1093
You can't specify target table 'order' for update in FROM clause

Is there any workaround?

 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
calculate the total first, then update?
 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How do I do? Do I simply run two queries like:


 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
now you have reformated the sql I can see it much clearer have you tried using table aliases for the order table, as you are using it twice, this could very likely be causing the problem.

but yes 2 seperate statements will do it, if aliases don't work.
 
Rob Spoor
Sheriff
Pie
Posts: 20610
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, I did the reformatting. That's because a) the line was too long, and b) the wrong code type (Java instead of SQL) was selected. Jeppe, can you try to split your lines next time? Combined with the right code type it makes your queries much easier to read.

Anyway, I believe you can do this in a similar way to the original code but without the second reference to the "order" table:
As you see, the `order` table is no longer referenced in the nested query; the `order` there is that of the outer query.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
never spotted the dropdown next to code tags, cool
 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rob.

This doesn't work.

The query below updates all orders even if orderDeliveryDate is previous to '2011-01-01'?

The subtotal of several orders was updated to "0". I guess it was the orders not having the productID = 2.


Rob Spoor wrote:Actually, I did the reformatting. That's because a) the line was too long, and b) the wrong code type (Java instead of SQL) was selected. Jeppe, can you try to split your lines next time? Combined with the right code type it makes your queries much easier to read.

Anyway, I believe you can do this in a similar way to the original code but without the second reference to the "order" table:
As you see, the `order` table is no longer referenced in the nested query; the `order` there is that of the outer query.
 
Rob Spoor
Sheriff
Pie
Posts: 20610
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Right. I misunderstood your requirements. You need to move part of that where clause to a where clause for the update statement. Assuming orderDeliveryDate belongs to table `order`:
This second nested query returns all order IDs with the given product; the where clause then makes sure only those orders with one of these order IDs are updated.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic