remove_wp_users_by_role.md

Remove Wordpress users by role

This scripts removes users with the role subscriber or customer using SQL-script in MySQL.

-- Identify users to be removed
SELECT wp_users.ID, wp_users.user_nicename 
FROM wp_users INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
WHERE wp_usermeta.meta_key = 'wp_capabilities' 
AND (wp_usermeta.meta_value LIKE '%subscriber%' OR  wp_usermeta.meta_value LIKE '%customer%') 
ORDER BY wp_users.user_nicename;

Create a temporary table containing the subscribers and customers that are to be removed.

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_users_to_remove AS (
  SELECT wp_users.ID, wp_users.user_nicename 
  FROM wp_users INNER JOIN wp_usermeta  ON wp_users.ID = wp_usermeta.user_id 
  WHERE wp_usermeta.meta_key = 'wp_capabilities'
  AND (wp_usermeta.meta_value LIKE '%subscriber%' OR wp_usermeta.meta_value LIKE '%customer%')  
);

Now one can do a simple sanity check of users to be removed

SELECT * FROM tmp_users_to_remove;

Delete users

-- Disable autocommit - https://dev.mysql.com/doc/refman/8.0/en/commit.html
START TRANSACTION; 
DELETE FROM wp_users WHERE ID IN (SELECT ID FROM tmp_users_to_remove);

-- Check the cleanup table wp_users
SELECT * FROM wp_users;

-- Not Ok? Then
ROLLBACK;
-- Else
COMMIT;