Message-ID: <3D585352.BC95E770@chipchat.com>
Date: Mon, 12 Aug 2002 20:31:14 -0400
From: Brian Cawthon <btc@chipchat.com>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, btc@chipchat.com
Subject: Problems using UPDATE and SUM
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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