Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin

Question

I have a wordpress table like so (phpmyadmin and MySQL)

| id |  meta_key | meta_value |
+----+-----------+------------+
| 1  | import_id | abc        |
| 2  | import_id | abc        |
| 3  | import_id | def        |
| 4  | import_id | xyz        |
| 5  | import_id | xyz        |
| 6  | import_id | xyz        |
| 7  | something | 123        |
| 8  | something | 234        |
+----+-----------+------------+

I need get the id of all duplicate rows where meta_key='import_id'

inorder to remove them from another table.

I want to keep the MIN(id) of each of the returned rows where 'meta_value' is the same

i.e the output should be:

| id |  meta_key | meta_value |
+----+-----------+------------+
| 2  | import_id | abc        |
| 3  | import_id | def        |
| 6  | import_id | xyz        |
+----+-----------+------------+

or just

| id |
+----+
| 2  | 
| 3  | 
| 6  | 
+----+

please help as while this may be a duplicate question I am still having trouble as my SQL is a bit rusty.

I can get duplicates where meta_key='import_id' like so:

SELECT id,meta_value
FROM TABLE 
WHERE meta_key='import_id'
GROUP BY meta_value
HAVING Count(meta_value) > 1

and I want from this the NON MIN(id) values

Answer 1

if you want to keep the row with the lowest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id > n2.id AND n1.meta_key = n2.meta_key

OR if you want to keep the row with the highest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id < n2.id AND n1.meta_key= n2.meta_key

Answer 2

Give a backup from your wp_usermeta table and try it:

DELETE FROM `wp_usermeta` WHERE `umeta_id` NOT IN (SELECT * FROM (SELECT MAX(`um`.`umeta_id`) FROM `wp_usermeta` um GROUP BY `um`.`meta_key`, `um`.`meta_value`) x)

Use MIN instead of MAX to keep row with the lowest id.