Need to bulk downgrade many users

Hello,

I need to bulk downgrade many users to subscriber level.

The WordPress installation has 15,597 users.

I need to downgrade/demote to subscriber level/role:

• by user name: 1,245 users
• by email address: 66 users

Obviously I don’t want to do this manually when there is phpmyadmin and SQL waiting for me :)

The closest SQL I was able to get was:

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
(
((wp_usermeta.meta_key)='wp_capabilities')
AND
((wp_users.user_login)="john")
OR
((wp_users.user_login)="jane")
OR
((wp_users.user_login)="jack")
OR
((wp_users.user_login)="jim")
);

AFTER backing up the database, when I ran that in phpmyadmin it said 52 rows affected. However all of the users in the database were demoted to subscriber.

Can you good SQL guru people share SQL that can do what I need to do?

Thank you,

Chris

Hello good people!

This can be done!

WORD TO THE WISE: BACKUP BACKUP AND BACKUP BEFORE DOING ANY SQL ON WORDPRESS.

I got this to work in phpmyadmin:

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
(
(wp_usermeta.meta_key)='wp_capabilities'
)
AND
(
(wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"
);

WORD TO THE WISE: BACKUP BACKUP AND BACKUP BEFORE DOING ANY SQL ON WORDPRESS.

Thank you to jason1178 and tagit at experts-exchange:

http://www.experts-exchange.com/Web_Development/Blogs/WordPress/Q_28188153.html

Thanks for sharing it! I’m no SQL expert, so I wouldn’t have been able to figure that one out. :P

Hey Cristian,

I’m a fair SQL hack — I look up what I need to do, do it and get back to work :)

Here’s how to do the same thing using the user’s email addresses.

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
((wp_usermeta.meta_key)='wp_capabilities')
AND
((wp_users.user_email)="johnsmyname@gmmail.com" Or (wp_users.user_email)="jane66@yaahoo.com" Or (wp_users.user_email)="jack@jackandjills.info" Or (wp_users.user_email)="jim@jimjimjimcom.com");

I didn’t conjure up this code. I worked with two very good SQL members at experts-exchange.com to get it:

How can I change many WordPress user levels using SQL in phpmyadmin?
http://www.experts-exchange.com/Web_Development/Blogs/WordPress/Q_28188153.html

I hope this code can help people :)

Chris

:)