0

I need help putting together an SQL that can delete users if they don't have (metakey1 or metakey2) and (does not have comments)

I have this SQL which does it for single meta_key

SELECT *
FROM wp_users LEFT JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
AND wp_usermeta.meta_key = 'metakey1'
WHERE wp_usermeta.user_id IS NULL

How can i extend the above SQL to do that?

  • Have you considered doing a WP CLI command instead and using get_user_meta and WP_User_Query? What you're wanting to do is going to result in a super expensive/slow query that probably won't finish in the time limit PHP has, and cause many issues ( assuming it can be understood ). Additionally, can you provide some context? What problem does this solve for you? – Tom J Nowell Mar 13 at 1:27
  • I need to delete thousands of users who registered when those meta were not set. I can run the SQL in batches. So i would love to have an SQL query that does it for me as described above. – Guru Surfer Mar 13 at 11:12
  • Two reasons why not using the WP CLI for this is first i get memory error since our users and usermeta are huge. Second i am doing these SQL on a staging database not connected to WP then copying from the staging DB to the live one and so SQL queries (taking some time) are running. – Guru Surfer Mar 13 at 12:50
0

So i got the answer and solution here https://stackoverflow.com/questions/55142768/sql-for-wp-to-delete-users-with-multiple-meta-keys-and-comments/55142835#55142835

SELECT u.*
FROM wp_users u
WHERE NOT EXISTS (SELECT 1
              FROM wp_usermeta um
              WHERE u.ID = um.user_id AND
                    um.meta_key IN ('metakey1', 'metakey2')
             ) AND
  NOT EXISTS (SELECT 1
              FROM wp_comments c
              WHERE u.ID = c.user_id
             );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.