Localizar registros duplicados numa tabela é uma operação comum para quem trabalha com banco de dados. Neste artigo vamos ver como localizar e excluir dados duplicados com SQL. Os códigos que você vai ver funcionam no MySQL e no SQL Server.

O que você vai aprender neste artigo?

1 - Localizar registros duplicados utilizando GROUP BY e HAVING

2 - Localizar registros duplicados utilizando ROW_NUMBER e PARTITION BY

3 - Localizar registros duplicados utilizando Commom Table Expression (CTE)

4 - Excluir registros duplicados

Antes de começar, vamos criar uma tabela de testes e popular com registros que serão utilizados em nossos testes.

MySQL

CREATE TABLE produtos_esportivos (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    nome varchar(255),

    codigo varchar(255),

    valor float(10,2)

);

SQL Server

CREATE TABLE produtos_esportivos (

    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    nome varchar(255),

    codigo varchar(255),

    valor float

);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Agasalho", "COD1", 40);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Jaqueta", "COD2", 40);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Meia", "COD3", 10);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Meia", "COD3", 10);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Camiseta", "COD4", 20);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Camiseta", "COD5", 25);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Camiseta", "COD6", 15);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Camiseta", "COD6", 15);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Chuteira", "COD7", 140);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Chuteira", "COD8", 120);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Chinelo", "COD9", 40);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bola de Futebol", "COD10", 80);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bola de Futebol", "COD11", 140);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bola de Futebol", "COD10", 80);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bola de Basquete", "COD12", 150);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bicicleta Aro 20", "COD13", 400);

INSERT INTO produtos_esportivos (nome, codigo, valor) VALUES ("Bicicleta Aro 26", "COD14", 600);

1 - Localizar registros duplicados utilizando GROUP BY e HAVING

A forma mais simples de identificar registros duplicados seria fazer um agrupamento de colunas e inserir uma coluna com contador.

SELECT nome, codigo, count(*) as total FROM

produtos_esportivos GROUP BY nome, codigo

Neste exemplo agrupamos os resultados por nome e codigo, e adicionamos uma coluna total para contar a quantidade de registros. Nos resultados, vamos ver que existem 3 registros com o número 2, estes são os registros que possuem repetições

Se desejamos ter como resultado apenas as repetições existem duas formas. A primeira seria utilizar esta consulta como uma subconsulta. Como nesta consulta criamos um campo chamado total, podemos usar ele para filtrar os registros maiores que 1.

SELECT * from (

SELECT nome, codigo, count(*) as total FROM

produtos_esportivos GROUP BY nome, codigo

) tabela WHERE total > 1

A segunda forma é utilizar HAVING. O HAVING deve ser aplicado depois do GROUP BY. Utilizando HAVING podemos retornar resultados onde um valor agregado atenda uma determinada condição. Em nosso caso, desejamos retornar os registros onde o nosso COUNT seja maior que 1.

SELECT nome, codigo, count(*) as total FROM

produtos_esportivos GROUP BY nome, codigo HAVING COUNT(*) > 1

2 - Localizar registros duplicados utilizando ROW_NUMBER e PARTITION BY

Uma outra forma de localizar registros duplicados é utilizar OVER e PARTITION BY. Utilizamos OVER quando queremos segmentar os dados e fazer operações com cada segmento. OVER utiliza como parâmetro a cláusula PARTITION BY. Utilizamos o PARTITION BY para definir qual a forma de segmentação e a ordenação deste segmento.

Um exemplo de sintaxe seria o seguinte:

<<Expressão de agrupamento>> OVER (PARTITION BY <> ORDER BY <>)

No exemplo a seguir vamos criar uma coluna chamada posicao. Vamos utilizar ROW_NUMBER como expressão de agrupamento. A segmentação será feita pelo campo código e a ordenação será pelo ID.

SELECT *

,ROW_NUMBER() OVER (

              PARTITION BY codigo

              ORDER BY (id)

            ) as posicao

FROM produtos_esportivos

Ao conferir o resultado, você vai ver que na maioria dos casos a coluna posicao retorna 1, já que a maioria dos produtos não tem repetição. Para as repetições, ele vai retornar 2, 3, e assim por diante. Perceba que nos casos de repetição, o número respeita a ordenação informada, que foi pelo campo id, ou seja, o registro que estiver com posição 2 tem um id maior que o seu similar com posição 1.

ROW_NUMBER serviu para numerar as linhas de resultado de forma sequencial de acordo com as regras de segmentação que definimos.

3 - Localizar registros duplicados utilizando Common Table Expression (CTE)

Uma terceira forma de identificar registros duplicados seria utilizando CTE. CTE é a abreviação de Commom Table Expression.  Ele é um conjunto de resultados temporário que existe apenas no contexto atual. Sua sintaxe é

WITH <> AS (<Cláusula SELECT com os dados desejados>)

Vamos ver um exemplo de utilização de CTE tomando como base um dos primeiros exemplos que vimos neste artigo.

WITH repetidos AS (

SELECT nome, codigo, count(*) as total FROM

produtos_esportivos GROUP BY nome, codigo

)

SELECT * from repetidos WHERE total > 1;

As Commom Table Expressions servem para deixar o trabalho com subconsultas mais simples.

Você também poderia fazer um CTE utilizando o exemplo anterior onde usamos OVER e PARTITION BY

WITH repetidos AS (

SELECT *

,ROW_NUMBER() OVER (

              PARTITION BY codigo

              ORDER BY (id)

            ) as posicao

FROM produtos_esportivos

)

SELECT * from repetidos WHERE posicao > 1;

4 - Excluir registros duplicados

Agora que vimos as diferentes formas que podemos utilizar para localizar dados repetidos chegou finalmente a hora de excluir. Será necessário fazer um DELETE utilizando como base uma subconsulta

Se eu tiver até duas repetições eu posso decidir se quero excluir o primeiro registro ou o último registro. Como a tabela tem ID, podemos selecionar os IDs que desejamos excluir.

No exemplo a seguir vamos listar o primeiro ID dos registros duplicados.

SELECT MIN(id) FROM produtos_esportivos GROUP BY nome, codigo HAVING COUNT(*) > 1

Se você deseja excluir apenas o primeiro registro duplicado você pode fazer da seguinte forma:

DELETE FROM produtos_esportivos WHERE id in (

SELECT MIN(id) FROM

produtos_esportivos GROUP BY nome, codigo HAVING COUNT(*) > 1

)

E caso a necessidade seja excluir o registro mais recente pode ser feito da seguinte forma:

DELETE FROM produtos_esportivos WHERE id in (

SELECT MAX(id) FROM

produtos_esportivos GROUP BY nome, codigo HAVING COUNT(*) > 1

)

A expressão de agrupamento MIN retorna o menor valor do campo informado de acordo com as regras de agrupamento definidas na consulta. Já o MAX retorna o maior valor.

Perceba que essa solução não atende os casos onde temos várias repetições. Neste caso, vamos precisar utilizar ROW_NUMBER e OVER. No exemplo que vimos, utilizamos ele para adicionar um campo posicao.  Se eu quiser saber todos os ids que tem posicao maior que 1, eu posso fazer desta forma:

select id from (

SELECT *

,ROW_NUMBER() OVER (

              PARTITION BY codigo

              ORDER BY (id)

            ) as posicao

FROM produtos_esportivos

    ) tabela where posicao > 1

Foi necessário fazer duas consultas. Fizemos uma consulta com OVER e PARTITION BY para criar o campo posicao e sobre esta consulta fizemos uma consulta para extrair ids onde posicao é maior que 1.

Utilizando estas consultas como base, vamos excluir qualquer registro que tenha o campo posicao com um valor maior que 1.

DELETE FROM produtos_esportivos WHERE id in (

select id from (

SELECT *

,ROW_NUMBER() OVER (

              PARTITION BY codigo

              ORDER BY (id)

            ) as posicao

FROM produtos_esportivos

    ) tabela where posicao > 1

)