Skip to content

Instantly share code, notes, and snippets.

@wpsmith
Created February 15, 2014 17:48
Show Gist options
  • Save wpsmith/9022638 to your computer and use it in GitHub Desktop.
Save wpsmith/9022638 to your computer and use it in GitHub Desktop.
SQL: Delete Unknown Users, i.e., users not found in comments or as an author of a post. Do not use this script for community sites or sites with Subscribers.
DELETE FROM wp_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM wp_comments);
DELETE FROM wp_usermeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM wp_comments);
DELETE FROM wp_links WHERE link_owner > 1 AND link_owner NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND link_owner NOT IN (SELECT DISTINCT user_id FROM wp_comments);
DELETE FROM myprefix_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM myprefix_comments);
DELETE FROM myprefix_usermeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM myprefix_comments);
DELETE FROM myprefix_links WHERE link_owner > 1 AND link_owner NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND link_owner NOT IN (SELECT DISTINCT user_id FROM myprefix_comments);
@wpsmith
Copy link
Author

wpsmith commented Feb 15, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment