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

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 duplicat开发者_开发问答e rows

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.

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      john@teratrax.com
 2    Mark Smith    marks@teratrax.com
 3    John Doe      jdoe@company.com

will return

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

Assuming you either have a table with those three columns, or that you can make and populate a table with those three columns, this query will show the duplicates.

select user_id, round, tournament_id
from yourtable
group by user_id, round, tournament_id
having count(*) > 1

This query selects all rows from the customers table that have a duplicate name but also shows the email of each duplicate.

SELECT c.name, c.email FROM customers c, customers d
WHERE c.name = d.name
GROUP BY c.name, c.email
HAVING COUNT(*) > 1

The downside of this is that you have to list all the columns you want to output twice, once in the SELECT and once in the GROUP BY clause. The other approach is to use a subquery or join to filter the table against the list of known duplicate keys.