Delete duplicate rows from mySQL database where a column is duplicate

2 Views

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’

Read More

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

Post Views: 2

Related posts

Leave a Reply

3 comments

  1. You need to use the MAX() aggregate function here, in order to get your desired output.

    You’re keep using the ‘term’ NON MIN (id) – now that confuses us a lot. This implies, you want all the ids which are NOT the MIN(id), where meta_key = ‘import_id’. If that is so, your output should have been similar to this:

     | id |
 +----+
 | 2  | 
 | 3  |
 | 4  | 
 | 5  | 
 +----+

    But if you need to return the following result as you have demonstrated inside your question:

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

    Then you must use the MAX() function.

    So your query should look something like this:

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

    Or if you want to return results from all columns, then try this:

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

    In this case, you need to use SELECT DISTINCT.

  2. The following query should return all the non-min ids for the pairs:

    select t.id
from table t
where t.meta_key = 'import_id' and
      exists (select 1
              from table t2
              where t2.meta_key = t.meta_key and
                    t2.meta_value = t.meta_value and
                    t2.id < t.id
             );