phpmyadmin Question posted in
The official documentation can be found here.

SQL for WP to delete users with multiple meta keys and comments – Phpmyadmin

I need help putting together an SQL that can to remove 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?

Tags:

2

Answers


  1. 0

    You can use in in the on clause:

    SELECT u.*
FROM wp_users u LEFT JOIN
     wp_usermeta um
     ON u.ID = um.user_id AND
        um.meta_key IN ('metakey1', 'metakey2', 'comments')
WHERE um.user_id IS NULL;

    You get no matches only if all the metakeys are missing, which I think is what you are asking for.

    EDIT:

    You seem to want:

    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
                 );

    I prefer NOT EXISTS if you are going to have multiple comparisons to different tables.

    Login or Signup to reply.
  2. 0

    Try doing a delete with an exists clause asserting the requirement of either of two keys:

    DELETE
FROM wp_users wp1
WHERE NOT EXISTS (SELECT 1 FROM wp_usermeta wp2
                  WHERE wp1.ID = wp2.user_id AND
                        wp2.meta_key IN ('metakey1', 'metakey2'));

    Note that the following where clause is no longer needed in the version of the query I wrote above:

    WHERE wp_usermeta.user_id IS NULL

    It is no longer needed because NOT EXISTS now handles the job which the exclusion join was handling previously.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search