Mass Delete WordPress Subscribers with MySql Query

Posted on 2011-03-21
Medium Priority
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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Ecommerce has truly become one of the most prosperous ways of monetizing your brand on the Internet. However, when it comes to it, auditing is undoubtedly the lifeblood of this type of business.  This article will help you to conduct your ecommerce …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

627 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