[wp-hackers] Clearing out bot users

[Sergey Biryukov]

On Wed, Feb 16, 2011 at 9:41 PM, Kevin Newman <CaptainN at unfocus.com> wrote:
> I'd thought of using a simple php loop and the WP functions, but it was more
> fun to try and come up with a single SQL query that could do it. :-)

DELETE u, mv
FROM wp_users AS u
JOIN wp_usermeta AS mv ON mv.user_id = u.ID
WHERE mv.meta_key = 'wp_capabilities' AND mv.meta_value LIKE '%subscriber%'
AND ( SELECT count(*) FROM wp_comments AS c WHERE c.user_id = u.ID ) <= 0
AND ( SELECT count(*) FROM wp_posts AS p WHERE p.post_author = u.ID ) <= 0

As Otto said, backup first :-)

Sergey