Snitz Forums 2000 - Removing Users with no Posts or Replies

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Help Groups for Snitz Forums 2000 Users

Help: Database: MS SQL Server

Removing Users with no Posts or Replies

New Topic

Topic Locked

Printer Friendly

Author

Topic

Carlos Miranda Levy
Starting Member

26 Posts

Posted - 03 February 2007 : 06:42:27

I used to have a large forum, which I eventually split into smaller forums because it evolved into separate communities. However, each new forum has the complete table of users from the original, resulting in thousands of users with no posts or replies, since their topics are now in another forum.

I already deleted all users with M_POSTS=0, but I still have thousands of users with M_POSTS>0 with no actual posts since those posts are no longer in the forum. Recalculating Forum Totals does not fix this issue.

Can someone confirm or think of any problem I could run into with any forum feature or functionality if I run the following SQL statement to effectively remove all users who have no post listed in the topics or replies tables?

I think it's pretty safe since I don't have archived topics or subscriptions in my forums and no mods dealing with user info. I already tested on a backup database and everything seems to be fine.


DELETE
 FROM FORUM_MEMBERS
  WHERE 
    (MEMBER_ID NOT IN (SELECT T_AUTHOR FROM FORUM_TOPICS))
   AND
    (MEMBER_ID NOT IN (SELECT R_AUTHOR FROM FORUM_REPLY))
   AND
    (MEMBER_ID NOT IN (SELECT MEMBER_ID FROM FORUM_MODERATOR))

Thanks.

Edited by - Carlos Miranda Levy on 03 February 2007 06:43:23

pdrg
Support Moderator

United Kingdom
2897 Posts

Posted - 03 February 2007 : 07:48:08

Safest way to approach these problems is to break them down,

first build your SQL as a SELECT (SELECT * FROM FORUM_MEMBERS...) and run it, checking it includes no false positives and still gets everybody

Then open a transaction and run your delete AND CHECK AGAIN

Finally commit the transaction

Also, make sure you're using DRI so you cannot orphan a child record, and you should be fine AFAIK. Oh, and it goes without saying, back it up before anything else, then test your code on a local copy, then exec it against your online db!

Shaggy
Support Moderator

Ireland
6780 Posts

Posted - 05 February 2007 : 05:07:00

And don't forget to check your FORUM_A_TOPICS and FORUM_A_REPLY tables for posts by the members as well.

Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”

Topic

New Topic

Topic Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.17 seconds.