CODEWITHSUNDEEP
phpmysql
Proffesor
Proffesor

Reputation: 2619

mysql select ONLY duplicate records from database

I'm trying to do something with the duplicate records in a mysql database. however, I don't want to delete the records, and only two columns are duplicate. How can I find just these records?

Upvotes: 6

Views: 13978

Answers (2)

kaj
kaj

Reputation: 5251

The general principle for finding duplicates is to just use group by and having count(*) > 1

If you just want to know the duplicate column values: 

select col1, col2
from table
group by col1, col2
having count(*) > 1

If however you want to see all fields where the two columns are duplicated:

select t.*
from @tbl t
where exists (select * 
              from @tbl d 
              where d.col1 = t.col1 and d.col2 = t.col2
              group by d.col1 
              having COUNT(*) > 1)

Upvotes: 3

Fabrizio
Fabrizio

Reputation: 3776

can you post more information about the table structure and what do you mean that some are duplicate but only by two columns ?

Anyway, you can look into the GROUP BY, COUNT, and HAVING

SELECT `duped_field1`, `duped_field2`, COUNT(*) `tot`
FROM `table`
GROUP BY `duped_field1`, `duped_field2`
HAVING `tot` > 1

Upvotes: 10

Related Questions