I have two tables that are related:

Parent LONG id LONG childCount LONG maxChildAge ...

Child LONG parentId LONG age ...

There can be thousands of parents and millions of children, that is why I have denormalized "childCount" and "maxChildAge". The values are too expensive to calculate each time the data is viewed so I update these values each time a Child is added, removed, or modified.

I currently have to update the Parent table with two queries like so:

SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = <x>;

UPDATE Parent SET maxChildAge = MAX, childCount = COUNT WHERE id = <x>;

Worse yet I might be updating the stats for several hundred Parents at a time, so I have to loop through the above where <x> is the current Parent.id in the batch. What I would like to do is something like the following (made up syntax):

UPDATE Parent SET maxChildAge AND childCount = COUNT WITH ( SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = Parent.id ) WHERE id IN ( <set_of_parents_to_update> );

Any suggestions?

TIA

R.