09-13-11 07:03 PM - Post#85628    

I want to delete all the records returned from this query:

Code:



 Select * FROM fbb_posts a LEFT JOIN fbb_users b ON (a.user_id = b.user_id) where (b.user_id IS NULL) and a.post_id >= 685644 

Simply

Code:



 Delete FROM fbb_posts a LEFT JOIN fbb_users b ON (a.user_id = b.user_id) where (b.user_id IS NULL) and a.post_id >= 685644 

does not work syntactically.

How can I pull this off? I'm trying to delete all the spam that has shown up this month. I've deleted all the spam accounts so there is no user_id attached to these posts anymore.

Jambands.ca Come waste your time with us. Follow me on Twitter!

09-13-11 08:25 PM - Post#85633    

    In response to bouche

An alternative to deleting the accounts is to flag all spammers accounts with "global ignore". Although it doesn't remove their posts, it does remove them from view.

You can then place those spam accounts in a group that cannot post.

I also approve every registration manually. That catches a lot of spammers.

09-15-11 12:13 AM - Post#85642    

    In response to Claude

Unfortunately, you can't delete posts with a single query, since there are multiple tables involved. When a post is deleted, the topic needs to be deleted too, and the total in the topic and/or total in the forum needs to be updated as well or pagination will get off.

Feel Better, Feel Younger, Sleep Better - Trans-D Tropin Visit www.BuyTransD.com - use coupon code 1004 to save $20

09-15-11 05:25 PM - Post#85652    

    In response to JoshPet

ouch. however, most spam is not a topic...in fact I think most or even all the spam are responses in a topic. Does that change anything?

Jambands.ca Come waste your time with us. Follow me on Twitter!

09-15-11 08:55 PM - Post#85662    

    In response to bouche

Well you could still blow out the posts, but the number of replies in the topic (and potentially the last reply info) could be wrong. There is a re-sync utility which would recalc the replies in topics, it runs through every topic though, so it's a process to start running and let it run overnight if you have a lot of topics.

Feel Better, Feel Younger, Sleep Better - Trans-D Tropin Visit www.BuyTransD.com - use coupon code 1004 to save $20

09-16-11 10:33 AM - Post#85670    

    In response to JoshPet

yeah, i ran that re-sync recently. took a couple of hours!

Jambands.ca Come waste your time with us. Follow me on Twitter!