2

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

  • 2
    This isn't really a WordPress question, you'd probably have better luck with it over at StackOverflow. – Milo Apr 17 '14 at 1:26
2

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
1

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.

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.