How do I write a SQL query to detect duplicate primary keys?

Question

Suppose I want to alter the table so that my primary keys are as follows

user_id , round , tournament_id

Currently there are duplicates that I need to clean up. What is the query to find all duplicates?

This is for MySQL and I would like to see duplicate rows

Answer 1

Technically, you don't need such a query; any RDBMS worth its salt will not allow the insertion of a row which would produce a duplicate primary key in the table. Such a thing violates the very definition of a primary key.

However, if you are looking to write a query to find duplicates of these groups of columns before applying a primary key to the table that consists of these columns, then this is what you'd want:

select
    t.user_id, t.round, t.tournament_id
from
    table as t
group by
    t.user_id, t.round, t.tournament_id
having
    count(*) > 1

The above will only give you the combination of columns that have more than one row for that combination, if you want to see all of the columns in the rows, then you would do the following:

select
    o.*
from
    table as o
        inner join (
            select
                t.user_id, t.round, t.tournament_id
            from
                table as t
            group by
                t.user_id, t.round, t.tournament_id
            having
                count(*) > 1
        ) as t on
            t.user_id = o.user_id and
            t.round = o.round and
            t.tournament_id = o.tournament_id

Note that you could also create a temporary table and join on that if you need to use the results multiple times.

Answer 2

SELECT name, COUNT(*) AS counter
FROM customers
GROUP BY name
HAVING COUNT (*) > 1

That's what you are looking for.

In table:

ID    NAME          email
--    ----          -----
 1    John Doe      [email protected]
 2    Mark Smith    [email protected]
 3    John Doe      [email protected]

will return

name         counter
----         -------
John Doe           2