0

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

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.

| improve this answer | |
0

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'.

| improve this answer | |

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.