Delete WordPress users with no posts by MySQL query

Back up your database regularly, and always before any upgrade. This query delete wordpress users (and they meta info) with no posts:

##delete meta info of user with no posts
DELETE FROM wp_usermeta
where user_id NOT IN (
	SELECT post_author
	FROM wp_posts
	GROUP BY post_author
);

##delete user with no posts
DELETE FROM wp_users
where ID NOT IN(
	SELECT post_author
	FROM wp_posts
	GROUP BY post_author
);

This query select users with no posts:

SELECT u.* 
FROM wp_users u
LEFT JOIN wp_posts p ON u.ID = p.post_author
WHERE p.ID IS NULL
GROUP BY u.ID

This query select users with no posts and no comments:

SELECT u.* 
FROM wp_users u
LEFT JOIN wp_posts p ON u.ID = p.post_author
LEFT JOIN wp_comments c ON u.ID = c.user_id
WHERE p.ID IS NULL AND c.comment_ID IS NULL
GROUP BY u.ID;
This entry was posted in WordPress and tagged , , , . Bookmark the permalink.

4 Responses to Delete WordPress users with no posts by MySQL query

  1. Messi

    FYI, the This query delete wordpress users (and they meta info) with no posts:

    Does not work. I had to backup my data.

    I used:

    DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts);

    DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts);

    • Thanks, result is same, distinct or group by.

    • This didn't work for me in WP3.8.1. It trashed my wp_user and wp_usermeta tables, and I had to restore.

Leave a Reply

Your email address will not be published. Required fields are marked *