From: Jeff Kowalczyk <jtk@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: updating a field with a SUM from another table
Date: Tue, 23 Sep 2003 12:15:53 -0400
Message-ID: <pan.2003.09.23.16.15.53.274637@yahoo.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jtk@yahoo.com
Subject: Re: updating a field with a SUM from another table
In-Reply-To: <pan.2003.09.23.16.15.53.274637@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox