Subject Re: how to add subtotal
Author Adam
--- In, "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.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.

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.