CODEWITHSUNDEEP
mysqlsqlwordpressphpmyadminduplicates
Sortitout
Sortitout

Reputation: 45

Delete duplicate rows from mySQL database where a column is duplicate

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

Upvotes: 2

Views: 676

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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

Upvotes: 1

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

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.

Upvotes: 1

Miller
Miller

Reputation: 35208

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

Upvotes: 0

Related Questions