BE SURE TO BACK UP YOUR DATABASE FIRST!!
To select users who have no posts or comments, you can use this:
SELECT *
FROM <code>wp_users</code>
WHERE ID NOT IN
(SELECT post_author FROM wp_posts
UNION SELECT user_id FROM wp_comments)
Then, replace ‘SELECT *’ with ‘DELETE’ to actually delete the records.
You will then have some ‘orphan’ records in wp_usermeta. Select them with this:
SELECT *
FROM <code>wp_usermeta</code>
WHERE user_id NOT IN
(SELECT ID FROM wp_users)
Then, replace ‘SELECT *’ with ‘DELETE’ to actually delete the records.
vtxyzzy,
Thank you much for the reply.
Have you tried this before? I was kind of hoping not to run these kind of queries against the database. Afraid something will go wrong.
I guess it seems too simple. With that said, will this be relatively safe to run?
Thanks again
I ran this against my test database before I posted the answer.
That being said, BE SURE TO BACK UP YOUR DATABASE FIRST!!! And, be sure you can restore from your backup.
vtxyzzy,
I get an error when running the SELECT query to test first.
Here is the error…
ERROR: Unknown Punctuation String @ 29
STR: </
SQL: SELECT *
FROM wp_users
WHERE ID NOT IN
(SELECT post_author FROM wp_posts
UNION SELECT user_id FROM wp_comments)
#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 ‘wp_users
WHERE ID NOT IN
(SELECT post_author FROM wp_posts
UNION’ at line 2
Sorry about that – MySql put backticks around the wp_users name. Take them out.
But there are no backticks in the code. Am I missing something here?
What’s the correct code for it then?
Thanks again
Double confusion!! MySQL puts in backticks. WordPress.org converts them to < code > and </ code >.
Correct syntax is this (nothing around wp_users):
FROM wp_users
