Updating Aggregate Data on MSSQL 2000

Ok, this is one of those little things that I keep forgetting how to do. So its the perfect new entry for my programmer’s blog :

How do you update aggregate data from another table (or view).

Here is the example:

update tablename set newaggregatevalue=(select aggregatefunction(field) from tblX where groupbyfield=tablename.fieldtogroupby)

The important thing to remember is that you can have a where clause referencing a field from the table that is going to be updated.

I am not sure if this is how you can also do it in mysql, I sure hope so.