To: pgsql-sql@postgresql.org
From: Jeff Kowalczyk <jtk@yahoo.com>
Subject: updating a field with a SUM from another table
Date: Tue, 23 Sep 2003 12:15:53 -0400
Lines: 33
Message-ID: <pan.2003.09.23.16.15.53.274637@yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.14.0 (I'm Being Nibbled to Death by Cats!)

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.