How can I undo accidentally duplicated user metadata key values?

Question

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?

Answer 1

This should do what you want:

global $wpdb;
$wpdb->query("
    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.

Answer 2

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'];
            $wpdb->query($sql); 
        }

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