MySQL update issue

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?

calculate the total first, then update?

How do I do? Do I simply run two queries like:

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.

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.

never spotted the dropdown next to code tags, cool

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:
UPDATE `order` SET orderTotalPrice = ( SELECT SUM(orderlinePrice) FROM orderline WHERE orderline.orderID = `order`.orderID AND orderline.productID = 2 AND orderDeliveryDate >= '2011-01-01' )As you see, the `order` table is no longer referenced in the nested query; the `order` there is that of the outer query.

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.