--- In firebird-support@yahoogroups.com, "Martijn
Tonies" <m.tonies@...> wrote:

>
> Hi,
>
> > I think that this is a fairly common job, but I don't know how to

do it in

> one sql.
> > eg, in master/detail invoice tables
> > table master has 2 fields (key1, tot)
> > table detail has 3 fields (key1, linenum, subtot)
> >
> > I want to update all records in master so that
> > each tot = sum(subtot) for master.key1=detail.key1
>
> It depends, do you want to store the value or not?
>
> If not, this could be a way:
>
> alter table master
> add total BIGINT COMPUTED BY ( (SELECT SUM(d.subtot) FROM detail

d WHERE

> d.key1= master.key1) )
>

YMMV but my past experience with computed fields that rely on selects
on other tables is that it takes a real performance hit.

http://tech.groups.yahoo.com/group/firebird-support/

In answer to the OP,
update master m set m.tot = (select sum(d.subtot) from detail d where
m.key1 = d.key1);

However you may want to consider simply writing a view containing the
total rather than a real field.

Adam