At 11:19 AM 15-01-02 +0000, you wrote:

>Hi,
>
>
>I tried to execute the following query in FB but it did not worked (I used to execute these kinkd of queries in Sybase) . I think this kind of queries are not supported in FB. Is there a workaround?
>
>UPDATE product, batch SET product.closing = sum(batch.closing)
>WHERE product.id = batch.product_id
>GROUP BY batch.product_id;

Gosh, no wonder the world needed SQL standards!

Use a correlated sub-query:

UPDATE product P
SET closing = (select sum(closing) from batch B
where B.product_id = P.product_id) ;

You don't need the GROUP BY (and it won't work in a correlated subquery, anyway) because the subquery itself is limited to the correlated product_id records. It will simply sum the selected records and poke the result into your update.

cheers,

Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________