Mass Delete WordPress Subscribers with MySql Query

Posted on 2011-03-21
Last Modified: 2012-05-11
Hi, I have this query to select my wordpress subscribers, but, now I need to delete them. Can someone help me change this to work?

I would need to delete their rows from the wp_users table and all associated data from the wp_usermeta table.

SELECT user_id, user_id 
AS ID, user_login, display_name, user_email, meta_value 
FROM wp_users, wp_usermeta 
WHERE wp_users.ID = wp_usermeta.user_id 
   AND meta_value LIKE '%subscriber%' 
ORDER BY wp_usermeta.user_id

Open in new window

Question by:adrian7474
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 35189299
CREATE TABLE temptable (temp_id VARCHAR(250) PRIMARY KEY);
INSERT temptable(temp_id)
SELECT wp_users.user_id FROM wp_users, wp_usermeta
WHERE wp_users.user_id = wp_usermeta.user_id
AND meta_value LIKE '%subscriber%'
ORDER BY wp_usermeta.user_id;
DELETE from wp_users where user_id in (select temp_id from temptable);
DELETE from wp_usermeta where user_id in (select temp_id from temptable);
DROP TABLE temptable;

Open in new window

LVL 31

Expert Comment

ID: 35224413
You can use wp_delete_user() , especially if you need to reassign any posts.

If you look at the code wp_delete_user() also calls clean_user_cache() before deleting the user

Expert Comment

ID: 35400846
After some thinking, I found a way that does that without you running any queries, scripts or anything...
Go to your Wordpress Dashboard, go to Users, click on Subscriber, check the checkbox left to the "Username", select from "Bulk actions" Delete, click Apply.

How to delete all subscribers within Wordpress Dashboard

Accepted Solution

adrian7474 earned 0 total points
ID: 35931296
Sorry, I have over ten thousand users. This would take all day to do. I ended up finding this query solution.

#Run this first ////
DELETE FROM insider_users
WHERE ID NOT IN (SELECT post_author FROM insider_posts)
# Then run this ///
FROM insider_usermeta WHERE user_id NOT IN
(SELECT ID FROM insider_users)

Open in new window


Author Closing Comment

ID: 35957287
Found my own solution.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: : Go t…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question