Problems using UPDATE and SUM - Mailing list pgsql-sql

From Brian Cawthon
Subject Problems using UPDATE and SUM
Date
Msg-id 3D585352.BC95E770@chipchat.com
Whole thread Raw
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Robert Treat
Date:
Subject: Re: Is this valid?
Next
From: "Lonh SENG"
Date:
Subject: Conversion