File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes MySQL update issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL update issue" Watch "MySQL update issue" New topic
Author

MySQL update issue

Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
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

Joined: Oct 21, 2008
Posts: 1107

calculate the total first, then update?
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
How do I do? Do I simply run two queries like:


Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Oct 27, 2005
Posts: 19697
    
  20

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.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

never spotted the dropdown next to code tags, cool
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
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

Joined: Oct 27, 2005
Posts: 19697
    
  20

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.
 
GeeCON Prague 2014
 
subject: MySQL update issue