Bite my bytes

What I learn by day I blog at night - A blog from Microsoft Consultant working from Ljubljana, Slovenia

  Home :: Contact :: Syndication  
  999 Posts :: 7691 Comments :: 235 Trackbacks

Search

Most popular posts
in last 360 days

Categories

My Projects

Archives

Stuff


Copyright © by David Vidmar
 
Contact me!
 
LinkedIn Profile
 
 
 

I decided to clean mine and some other blogs .Text databases after I've installed SQL-trigger based solution for blocking black listed words.

Maybe someone will find these SQL staments useful. Just change the green table and field names in italic to match your installation. Use with care!

This one will generate INSERT statements for your blacklist so you can copy the list to another database. Copy/paste and run on target database.

SELECT 'INSERT INTO MY_ANTISPAM_TABLE VALUES (''' + SpamWord + ''')'  FROM BlackList

This one will generated DELETE statements for all words in black list table. Copy/paste the results and run and trackback and comments entries should dissapear.

SELECT 'DELETE FROM blog_content WHERE Title LIKE ''%' + SpamWord + '%''' FROM BlackList ORDER BY ID

Since .Text stores the feedback counts in the field so the numbers are not recalculated in every query, we have to update that count. This is SELECT the will get you the right numbers and some other info.

  SELECT BCP.Title,
         BCP.ID,
         BCP.ParentID,
         BCP.FeedBackCount,
         COUNT(BC.ID)
    FROM blog_Content BCP LEFT JOIN
         blog_Content BC ON BCP.ID = BC.ParentID
   WHERE BCP.ParentID = -1
GROUP BY BCP.FeedBackCount,
         BCP.ID,
         BCP.Title,
         BCP.ParentID,
         BCP.PostType

Now copy the results to Excel starting in cell A1. Paste this formula in cell G1, copy it down as far as needed. You have generated the UPDATE statements for all your posts. Copy/paste to Query Analyzer or equivalent and run.

="UPDATE blog_Content SET FeedBackCount = " & E1 & " WHERE ID = " & B1

My blog is relatively clean now. Have I missed something?

Posted on Tuesday, June 28, 2005 11:40 PM | Filed under: Blogging |
Comments have been closed on this topic.