mysql - SQL query to delete users with multiple meta keys and comments

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?

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?

• query
• mysql
• sql

Share Improve this question asked Mar 12, 2019 at 22:54 Guru SurferGuru Surfer 111 bronze badge 3

• 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 ♦ Commented Mar 13, 2019 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 Commented Mar 13, 2019 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 Commented Mar 13, 2019 at 12:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

So i got the answer and solution here https://stackoverflow/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
             );