Re: trying to do an update a bit confused.

On 4/19/05, Joel Fradkin <jfradkin@wazagua.com> 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 ;)