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


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