Mysql select ONLY duplicate records from database

Question

I am trying to do something with duplicate entries in a mysql database. however, I do not want to delete records, and only two columns are duplicated. How can I find only these entries?

Answer 1

can you post additional information about the structure of the table and what do you mean that some of them are duplicated, but only by two columns?

In any case, you can see GROUP BY , COUNT and HAVING

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

Answer 2

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

If you just want to know the values ​​of repeating columns:

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

If you want to see all fields in which 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)