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
[code=oracle]

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);
[/code]

[QVeen72]

Hi,

Try This :

[code=oracle]
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
[/code]

Regards
Veena