Batch Delete users and re-assign posts to single user account WordPress

Justin Walmsley comments:

No I havent for this site as i thought adding something like W3TC would effect the cache, as the FeedWordPress plugin has it's own caching system, to see if posts have been updated or not.

Peter Michael comments:

I would try installing & configuring W3TC first as it provides DB cache as well.

If you want to go your route, you can delete users & assign posts with this SQL script:

<strong>WARNING: This will irreversibly DELETE all your subscribers!</strong>


# Delete all subscribers
DELETE 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 = 'a:1:{s:10:"subscriber";b:1;}' );

# Assign all posts to admin (replace ADMINID with your own admin ID)
UPDATE wp_posts SET post_author = ADMINID;


HTH

Justin Walmsley comments:

Hi Peter,

thanks for this, i will try W3TC, but it still might be an option to delete all the old subscribers.

With regards to the Admin ID is this the name, ie "admin" or a numerical number, like a post id?

thanks

Peter Michael comments:

Admin ID is an Integer (numerical). You can see the ID when you hover over the username link in wp-admin in the status bar (user_id=)

Justin Walmsley comments:

Hi Peter,

I am getting an SQL error when trying to run the code:

<blockquote>Error

SQL query:

DELETE 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 = 'a:1:{s:10:"subscriber";b:1;}'
);

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN wp_usermeta on wp_users.ID = wp_usermeta.user_id

WHERE ( wp_userme' at line 3 </blockquote>

the code i used was:
DELETE 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 = 'a:1:{s:10:"subscriber";b:1;}' );

UPDATE wp_posts SET post_author = 1265;

Peter Michael comments:

Sorry, my bad. Try this:

DELETE wpu FROM wp_users AS wpu
INNER JOIN wp_usermeta AS wpm ON wpu.ID = wpm.user_id
WHERE wpm.meta_key = 'wp_capabilities' AND wpm.meta_value = 'a:1:{s:10:"subscriber";b:1;}';
UPDATE wp_posts SET post_author = 1265;


Justin Walmsley comments:

Hi Peter,

Still not working, could it be that my table prefix is mmwc_

I'm getting the error
<blockquote>Error

SQL query:

DELETE wpu FROM wp_users AS wpu INNER JOIN wp_usermeta AS wpm ON wpu.ID = wpm.user_id WHERE wpm.meta_key = 'wp_capabilities' AND wpm.meta_value = 'a:1:{s:10:"subscriber";b:1;}';

MySQL said: Documentation
#1146 - Table 'mmwcops_wp.wp_users' doesn't exist </blockquote>

Peter Michael comments:

Yes. Adapted for your prefix:


DELETE wpu FROM mmwc_users AS wpu INNER JOIN mmwc_usermeta AS wpm ON wpu.ID = wpm.user_id WHERE wpm.meta_key = 'wp_capabilities' AND wpm.meta_value = 'a:1:{s:10:"subscriber";b:1;}';
UPDATE mmwc_posts SET post_author = 1265;


Again, I don't think the users in the DB are your site's performance issue.

Justin Walmsley comments:

The script transferred all of the posts to the admin account, but has not deleted the users.

The website is working faster now I have set up W3TC especially DB Caching :)

Any script for just deleting the Subscribers? I just want to get rid of them to tidy up the system really ;)

Peter Michael comments:

You can add this to the top of your functions.php file in your theme:


function remove_subscribers() {
global $wpdb;
$args = array( 'role' => 'Subscriber' );
$subscribers = get_users( $args );
if( count($subscribers > 0 ) ) {
require_once( ABSPATH.'wp-admin/includes/user.php' );
$i = 1;
foreach( $subscribers as $subscriber ) {
wp_delete_user( $subscriber->ID );
$i++;
}
echo $i.' Subscribers deleted';
} else {
echo 'No Subscribers deleted';
}
}
remove_subscribers();


Then load any page on your blog and it will remove all subscribers and output on the top of the page/post how many got deleted from the DB.

After that, delete the code or simply comment out the function call by adding two slashes to the front:


// remove_subscribers();


Please consider raise your prize money a little. ;-)

Peter Michael comments:

Cleaner version:


function remove_subscribers() {
global $wpdb;
$args = array( 'role' => 'Subscriber' );
$subscribers = get_users( $args );
if( !empty($subscribers) ) {
require_once( ABSPATH.'wp-admin/includes/user.php' );
$i = 0;
foreach( $subscribers as $subscriber ) {
if( wp_delete_user( $subscriber->ID ) ) {
$i++;
}
}
echo $i.' Subscribers deleted';
} else {
echo 'No Subscribers deleted';
}
}
remove_subscribers();