It feels great when I realized that I’ve just found out a new way of doing things. But it feels kinda sucks when that “new way” is apparently has been around for quite sometime. It did made me feel bad about myself a little. Because that means either I have not been looking hard enough, or I didn’t even bother to find out because I can use my old method to achieve the same result (a.k.a. Comfort Zone Leaving Avoidance Syndrome). 😀

Since this problem is related to MS SQL Database, we need to imagine that we have this condition:

1. There are two tables, Categories (key: CategoryID) and Posts (has a CategoryID field)
2. Posts to Categories is in 1-to-1 relationship

Now we are required to keep track of how many Posts under a particular Category. For that purpose, an integer field called PostCount is added to Categories table. In order to maintain the correct PostCount value, triggers would need to be added in Posts table. These triggers will be invoked whenever a Post is created, updated and deleted. But before that, we need to run an update query to initialize the PostCount value. And this update query is where I learned my “no-longer-new way”.

If you asked me to do this last week, I would definitely insisted that this is unworkable in SQL. I would then quickly write a small program to:

1. Retrieve all the Categories
2. Iterate each Category, and count how many Posts with this Category
3. Update Category

Fortunately, the request insisted that it must be done in SQL. Thus, it forced me to trial and error, as well Googling for it. As I found out today after many trials, above steps could be done in SQL. Here’s how:

Update CCC
Set CCC.PostCount = PPP.Counted
From Categories CCC
Inner Join (
   Select CategoryID, Count(*) as Counted
   From Posts
   Group By CategoryID
) PPP
on CCC.CategoryID=PPP.CategoryID;

The joy of finding out this method was significantly reduced when I found out that this guy blogged about it almost 5 years ago! 😀

Cheers!

Incoming Search

sql