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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
