Thread: updating a field with a SUM from another table

updating a field with a SUM from another table

From
Jeff Kowalczyk
Date:
My SQL is apparently a bit rusty, can anyone advise how to refactor
this updatecharges query to work? I need to update a total-charges field
in my orders table with the sum of the line-item charges in another table.
The tables are related by the orderid column.

Thanks for any help you can provide with updatecharges.

Query: updatecharges (query I'm having trouble with)
--------------------
UPDATE orders
RIGHT JOIN orderchargetotals
ON orders.orderid = orderchargetotals.orderid
SET orders.chargeasbilled = orderchargetotals.orderchargeasbilled;

Query: orderchargetotals
------------------------
SELECT ordercharges.orderid,
SUM(ordercharges.orderchargeasbilled) AS orderchargeasbilled
FROM ordercharges
GROUP BY orderid
ORDER BY orderid;

Table: orders
-------------
orderid, chargeasbilled, field1, field2, ...

Table: ordercharges
-------------------
orderchargeid, orderid, chargecode, ordercharge

BTW, I do know its counter to relational precepts to store a total one
could calculate at query-time, but I have reasons to do so at the the
moment.