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