How Can We Help?
Deleting WordPress Subscribers via mySQL
If your WordPress site has accumulated a large number of subscribers — particularly from a membership plugin, newsletter signup, or open registration — you may need to bulk delete them from the database. This script does that efficiently via MySQL without needing a plugin or PHP.
This script will delete all WordPress subscribers who have never commented on a post. Subscribers who have commented are preserved.
Before you begin
- Take a full database backup before running any SQL script. This operation cannot be undone.
- You will need access to your database via phpMyAdmin, MySQL Workbench, or a similar tool
- The script assumes standard WordPress table prefixes (wp_). If your installation uses a custom prefix, update the table names accordingly.
- Read through the script and its comments before running it — the delete lines are intentionally commented out so you can do a dry run first
The script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* Delete all WordPress subscribers that have never commented on a post. The actual delete lines are commented out intentionally. Do a dry run first, then uncomment them for production. If your WordPress installation uses a table prefix other than wp_ you will need to update the table names below. */ -- Create a temporary table to hold the users to be deleted CREATE TEMPORARY TABLE wp_delete_users (id BIGINT(20)); -- Get all subscribers from the database INSERT INTO wp_delete_users SELECT ID FROM wp_users INNER JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.id WHERE wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value = '0'; -- Remove subscribers who have commented on a post from the delete list DELETE d.* FROM wp_delete_users d INNER JOIN wp_comments c ON c.user_id = d.id; -- Uncomment the lines below when you are ready to run for real -- DELETE u.* FROM wp_users u INNER JOIN wp_delete_users d ON u.id = d.id; -- DELETE u.* FROM wp_usermeta u INNER JOIN wp_delete_users d ON u.user_id = d.id; -- Clean up DROP TEMPORARY TABLE IF EXISTS wp_delete_users; |
How to use this script
- Back up your database
- Open your database management tool and select your WordPress database
- Paste the script into the SQL query window and run it with the delete lines still commented out — this is your dry run
- Verify the results look correct
- Uncomment the two delete lines and run the script again to perform the actual deletion
Important notes
- This script only targets users with the subscriber role. If you have modified WordPress roles or want to target a different role, review the script carefully before running it.
- If your site uses WooCommerce, a membership plugin, or an LMS, subscribers may have associated order or enrollment records. Deleting them at the database level may leave orphaned data. Contact us if you are unsure.
- Subscribers are assumed not to be post authors. If you have subscribers who are also authors, add an additional exclusion before uncommenting the delete lines.
Related articles
- How to change your WordPress login
- Adding a WordPress user via PHP
- Usernames to avoid when configuring WordPress
Need help managing your WordPress database or site? Glimmernet offers WordPress maintenance and managed hosting services.
