Delete duplicate rows from mySQL database where a column is duplicate

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

Choudhury Saadmaan Mahmid · Accepted Answer · 2014-04-17 05:52:13Z

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.

Gordon Linoff · Accepted Answer · 2014-04-17 12:06:04Z

1

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
             );

edited Apr 17, 2014 at 12:06

answered Apr 17, 2014 at 1:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Sortitout Over a year ago

I get 0 rows returned :/

Miller · Accepted Answer · 2014-04-17 05:37:23Z

0

SELECT MAX(id), meta_value, COUNT(*) AS DUPS
FROM TABLE 
WHERE meta_key='import_id'
GROUP BY meta_value
HAVING DUPS > 1;

answered Apr 17, 2014 at 5:37

Miller

35.3k4 gold badges42 silver badges61 bronze badges

Collectives™ on Stack Overflow

Delete duplicate rows from mySQL database where a column is duplicate

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related