I've spotted that some of my users have duplicate metadata key values (my only fault because i inserted data directly to the wp_usermeta table!). This query for example

SELECT * FROM  `wp_usermeta` WHERE  `user_id` =2327x LIMIT 0 , 30

returns two rows

meta_id - user_id - meta_key - meta_value
178208 - 23270 - bhaa_runner_status - D
187959 - 23270 - bhaa_runner_status - D

I have a number of users who are effected by this and i'm wondering is there a SQL update or delete i can run to spot this case and delete the larger 'meta_id' row?

2 Answers 2


This should do what you want:

global $wpdb;
    DELETE FROM $wpdb->usermeta
    WHERE meta_id NOT IN (
        SELECT MIN(meta_id)
        FROM $wpdb->usermeta
        GROUP BY user_id, meta_key, meta_value

Maybe you should first back up your table, though.


I found the following helped:

        $sql = "SELECT MAX(umeta_id)
            FROM wp_usermeta
            GROUP BY user_id, meta_key, meta_value
            HAVING COUNT(user_id) > 1";

        $um = $wpdb->get_results( $sql, ARRAY_A );

        foreach ($um as $user) {
            $sql = "DELETE FROM wp_usermeta WHERE umeta_id = " . $user['umeta_id'];

The count clause is useful to ensure that you are getting duplicates. The MAX gives you the larger 'meta_id'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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