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

> Hello everybody
>
> I have a table PRODUCTS (Product_ID, Product_Name, LastDate)
>
> A table MOVIMCAB (Movim_ID, Date) -- parent table
>
> A table MOVIMDET (Movim_ID, Product_ID) -- child table
>
> Each product can be several times in MOVIMDET
>
> Tables MOVIMCAB and MOVIMDET can be relationated by the Movim_ID column.
>
> What I need is:
> - Each product having the last date, as found in MOVIMCAB
>
> By example, if the date were 01/01/2010, 05/02/2010, 06/03/2010 then I
> need
> that the column 'LastDate' of PRODUCTS have 06/03/2010
>
> What I could to write for update the 'LastDate' column of the table
> PRODUCTS
> with the last date in which that product can be found?
>
> In other words, each product with its last date.
>
> I use Firebird 2.1.3
>
> Thanks in advance.

How about something like (untested)

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

HTH

Woody (TMW)