sql query to remove all posts and replies

[chikuru]

sql query to remove all posts and replies of a member?
Its not possible via web interface because it is timing out.
Number of posts to be removed are 100k+

[Dragonsys]

DELETE FROM " . TABLE_PREFIX ."posts WHERE userid = YOUR_USER

If running from ACP,just change YOUR_USER to the userid you wish to remove.
If running from phpMyAdmin or such, change " . TABLE_PREFIX ." to the actual table prefix

[chikuru]

Which should I rebuild after executing this?
The threads are still showing in forumdisplay and says invalid thread specified when clicked

[Dragonsys]

Post counts and search index

[chikuru]

Post counts and search index

I rebuilt both Post Counts and Search Index after running that query but the deleted posts still showing in forumhome and forumdisplay.. :confused:

[Dave]

Have you tried "Rebuild Thread Information" and "Rebuild Forum Information"?

[chikuru]

Have you tried "Rebuild Thread Information" and "Rebuild Forum Information"?

Tried that but didnt help..
You have other suggestion Dave?

[Dave]

Wait so you executed the following query right? DELETE FROM posts WHERE userid = YOUR_USER

Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
DELETE FROM thread WHERE postuserid = USERID_OF_USER

[chikuru]

Wait so you executed the following query right? DELETE FROM posts WHERE userid = YOUR_USER

Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
DELETE FROM thread WHERE postuserid = USERID_OF_USER

the 2nd query did it. Then rebuilt threads and forums. It worked!
Thanks for your great help Dave! As well as Dragonsys for always helping the vb community :up:

[Dragonsys]

Wait so you executed the following query right? DELETE FROM posts WHERE userid = YOUR_USER

Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
DELETE FROM thread WHERE postuserid = USERID_OF_USER

Thanks Dave, I forgot that one.

[squidsk]

And since the thread likely had posts not by the user you're removing deleting posts for you now have orphaned posts (i.e. posts not attached to an existing thread).

To delete them run the following:

DELETE FROM post WHERE NOT threadid IN (SELECT threadid FROM thread)

Note: this will remove all posts that are not associated with a thread, not just the ones related to threads you have just deleted.

[chikuru]

Great! Thanks for the addition squidsk!
Im sure this thread will also help other forum owners who deletes thousands of spams in one go.