At 04:14 PM 20-01-02 +0000, you wrote:

>Hi,
>
>Is there any equivalent command in FB for Truncating Table ?

No. But scan the archive because I seem to recall Ann Harrison recently posted a suggestion for achieving it. 2 - 3 weeks ago, I think.

>UPDATE product P
> SET closing = (select sum(closing) from batch B
> where B.product_id = P.product_id) ;
>
>How do we handle when we need to update one more column in the product table from
>table batch?

1. Multiple correlated sub-selects on each column required (slow)
2. Write a stored procedure with a FOR loop (fast)

create procedure update_it
as
declare variable prod_id numeric(18,0);
declare variable x numeric(18,2);
declare variable y sometype;
declare variable z someothertype;
begin
for select product_id, sum(closing), col1, col2 from batch
group by product_id
into :prod_id, :x, :y, :z
do begin
UPDATE product
SET closing = :x,
col1 = :y,
col2 = :z
WHERE product_id = :prod_id;
end
end

Just ensure that all the columns selected from Batch can be aggregated by product_id.

regards,
Helen

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