Updating a column based on value in another table

[cpeters]

Can anyone see what's wrong with this update statement? It works on my DB2 database, but not on my Oracle 9i database. Any help would be much appreciated. Thx, Coleen

I want to set PLDG_BILL_AMT = the sum of its gifts divided by the nbr of its gifts

 
update SUMMIT.PLDG a 
set a.PLDG_BILL_AMT = ((select sum(b.GIFT_AMT) from SUMMIT.GIFT b where a.PLDG_NUM = b.PLDG_NUM) / (select count(*) from SUMMIT.GIFT c where a.PLDG_NUM = c.PLDG_NUM))  
where a.PLDG_NUM = 2
  AND a.PLDG_BILL_AMT = 0 
  AND a.PLDG_NUM in (select b.pldg_num from SUMMIT.GIFT b where a.PLDG_NUM = b.PLDG_NUM);
 

[QVeen72]

Hi,

Try This :

update SUMMIT.PLDG a 
set a.PLDG_BILL_AMT = (select sum(b.GIFT_AMT) / count(*)  from SUMMIT.GIFT b where a.PLDG_NUM = b.PLDG_NUM Group By  b.PLDG_NUM Having Count(*) >0 )  
where a.PLDG_NUM = 2
AND a.PLDG_BILL_AMT = 0 
 

Regards
Veena