Re: trying to do an update a bit confused.

On 4/19/05, Joel Fradkin  wrote:
>  
>  
> 
> update tblcase  set merchandisetotal = 
> 
> ( 
> 
> COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount 
> 
>            FROM tblmerchandise m 
> 
>           WHERE m.caseid = tblcase.caseid AND m.clientnum::text =
> tblcase.clientnum::text), 0.0) 
> 
> ) 
> 
>   

Put the coalesce inside the select:
update tblcase  set merchandisetotal =     (SELECT COALESCE(sum(m.quantity::numeric * m.amount),0.0) AS
merchandiseamount       FROM tblmerchandise m      WHERE m.caseid = tblcase.caseid AND m.clientnum::text =
tblcase.clientnum::text)

-- 
Regards,

DBA* Jaime Casanova
(DBA: DataBase Aniquilator ;)