Find duplicate records in SQL table

By | 5 November, 2010

There are a few ways to do this, but i am leaving this one here so its easy to find.

SELECT nome,

COUNT(nome)

FROM Patrimonio

GROUP BY nome

HAVING ( COUNT(nome) > 1 )

This returns a list with two columns of records that exist in table “Patrimonio” more than once. A column for “nome” and one for number of occurrences.

EDIT/UPDATE: In case you want to go further and gather all non-duplicates in a different table, you do this:

INSERT INTO emails_ok(id_email,email) SELECT id_email,email FROM emails GROUP BY email

(table “emails_ok” should already exist)

This grabs all non-duplicates from table “emails” and inserts them in “emails_ok”