update with count

[sammybee]

any ideas why I can't use this update statement?

update sb_void_offs
set offcount = blah
(Select pro_propref, count (oof_refno) blah
FROM sb_void_offs, organisation_offers
WHERE pro_refno = oof_pro_refno(+)
AND oof_offer_date BETWEEN voiddate AND letdate
group by pro_propref
)

cheers

Sam

 

[LKBrwnDBA]

Because it is incorrect -- try this:

update sb_void_offs v
   set offcount = (
       Select count (oof_refno)
         FROM organisation_offers o
        WHERE v.pro_refno = o.oof_pro_refno
          AND oof_offer_date BETWEEN voiddate AND letdate)
 where exists (          
       Select 1
         FROM organisation_offers o
        WHERE v.pro_refno = o.oof_pro_refno
          AND oof_offer_date BETWEEN voiddate AND letdate)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

You can not assign 2 values (pro_propref, blah) to a single field (offcount).

Regards, Dima

http://seminihin.narod.ru

 

[sammybee]

Cheers All, this worked perfectly

p.s. love the proverb;-)