From: Brian Cawthon <btc@chipchat.com>
To: pgsql-sql@postgresql.org
To: btc@chipchat.com
Subject: Problems using UPDATE and SUM
Date: Mon, 12 Aug 2002 20:31:14 -0400
Message-ID: <3D585352.BC95E770@chipchat.com> (raw)
I am having a SUM problem also.
Here is what I am trying to do.
There are two tables: cust_rfq1_2 and inventory1
cust_rfq1_2 has four columns: rfq_id(int4),qty(int4),part_id (varchar
30),rev (varchar 2)
inventory1 has three columns: part_id (varchar 30),rev (varchar 2),
qty_instock (int4)
Data in the cust_rfq1_2 looks like this:
4, 5,parta,01
4,10,parta,01
4,10,partb,01
Data in the inventory1 looks like this:
parta,01,100
partb,01,100
When I use the select
cust_rfq1_2.part_id,cust_rfq1_2.rev,SUM(cust_rfq1_2 from cust_rfq1_2
where cust_rfq1_2.part_id=inventory1.part_id and
cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id ='4' group by
inventory1.part_id,inventory1.rev"
I get good results:
part_id,rev,sum
parta,01,15
partb,01,10
However, when I use this sql statement for update:
"update inventory1 set qty_instock=inventory1.qty_instock +
cust_rfq1_2.qty where cust_rfq1_2.qty = ANY (select SUM(cust_rfq1_2.qty)
from cust_rfq1_2 where cust_rfq1_2.part_id=inventory1.part_id and
cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id='4' )"
I get this:
inventory1 Results After the update
part_id,rev,qty
parta,01,100
partb,01,110
inventory1 Results before the update
part_id,rev,qty
parta,01,100
partb,01,100
inventory1 Results Should be After update
part_id,rev,qty
parta,01,115
partb,01,110
Any suggestions would be appreicated.
Tyge Cawthon
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: btc@chipchat.com
Subject: Re: Problems using UPDATE and SUM
In-Reply-To: <3D585352.BC95E770@chipchat.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