> > > 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.

I had this in an application with no problems whatsoever.

Of course, it was an indexed select and the "total" column was only
asked for when needed.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com