Updating parent property values in SQL

Question

Hello,

I have a SQL Server 2008 database with 2 tables. These tables are defined like this:

Device
------
ID
Name
Description
TotalApplications

Application
-----------
ID
Name
DeviceID

I recently added the "TotalApplications" column in an effort to speed up a query that is taking WAY too long. The number of applications associated with a device will not change very often. When it does, I will need to write a trigger to handle this. But for now, I just need to populate my database.

Currently, the TotalApplications associated with each device is 0. Clearly I need to update this value for all of my device records. However, I cannot figure out how to do this. Can someone please explain how I can update the TotalApplications value for my Device records?

Thank you SO much for your help!

Answer 1

Use:

UPDATE DEVICE
   SET totalapplications = (SELECT COUNT(*)
                              FROM APPLICATION a
                             WHERE a.deviceid = DEVICE.id)
 WHERE totalapplications = 0

Answer 2

OMG Ponies answer would certainly work for this, but if this were my db I would avoid doing adding the column and using the trigger. Instead use indexing or I cant get there with indexes I would use an indexed view. Let me know if you want me to post that.