Subject RE: [firebird-support] how to add subtotal
Author sasha
> Hello,
> 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

update master set
tot = (select sum(subtot) from detail where detail.key1 = master.key1)

But what you should really consider is dropping master.tot, and when you
need total then you select it with

select master.key1, sum(subtot) tot
from master
left join detail on detail.key1 = master.key1
group by master.key1

Or if you feel you absolutely must have master.tot then set up after insert
or update or delete trigger for detail table which will update it's master