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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
