Indeed this is a DSS warehouse application. I was able to get the update to work with something like
update parent set childcount = (select count(pcode) from child
where child.pcode = parent.pcode)
-Isaac
>Chris,
>This is generally the case in a transaction processing system. However, it is common practice (and generally encouraged) in a decision support system to keep aggregates pre-processed in the parent table. The raw data is still available but not used quite as often.
>
>*snip*
>>Not that I am a relational theory purist, but maintain a count of child records in the parent is generally considered "not a good idea" :). You can always get this data with the following SQL statement:
>>
>>SELECT Child.PCode, COUNT(*)
>> FROM Child
>> GROUP BY PCode
>>
>>This solution works well for a couple of reasons:
>>
>>1. Space is saved - although it may be minor, you do not need the count column in the parent table.
>>2. Speed - Every time a child record is added or deleted (which may be often), you don't have the added overhead of counting all the child records and updating the parent.