From: "W O" <sistemas2000profesional@...>
Sent: Tuesday, April 05, 2011 11:41 AM
To: <firebird-support@yahoogroups.com>
Subject: Re: [firebird-support] How to update data?

> Hello Woody
>
> It works very well when there are dates, but the column LastDate can not
> be
> null.
>
> The PRODUCTS table has the column FirstDate, too.
>
> How would be the sentence if:
> - There are dates in MOVIMCAB then update PRODUCTS.LASTDATE with the last
> date of MOVIMCAB for that product
> - There are not dates in MOVIMCAB, then update PRODUCTS.LASTDATE with
> PRODUCTS.FIRSTDATE
>

Try using COALESCE: (not sure it will work in this situation though)

Update Products A set A.LastDate = (Select COALESCE(Max(B.Date),
A.FirstDate) from MovimCab B
Inner Join MovimDet C on (B.Movim_ID = C.Movim_ID) and (C.Product_ID =
A.Product_ID))

If this doesn't work, you might have to resort to creating a stored
procedure that you could run instead.

HTH

Woody (TMW)