Hi,

Can we update more than one column in the product table
from the batch table?

Thanks for your kind reply

Ben



On Tue, 15 Jan 2002 Helen Borrie wrote :

> 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
> _______________________________________________________
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>