Como eliminar linhas duplicadas e repetições no SQL Server

Olá Pessoal,

Hoje demonstrarei como eliminar linhas duplicadas e repetições em uma tabela no SQL Server. Normalmente esse é o tipo de dúvida comum para iniciantes e cuja a solução quase sempre é muito trivial. De fato há muitas soluções triviais para esse problema, mas nem todas as situações em que existam linhas duplicadas e repetições são tão fáceis de resolver. Utilizarei alguns exemplos para demonstrar como eliminá-las desde as situações mais simples até as mais complexas.

Situação 1 – Única tabela com todos os campos repetidos

Essa é a situação mais fácil de resolver e sem dúvida a única em que todas as soluções são aplicáveis. Vejamos um script para demonstrá-la:

— Cria uma tabela
CREATE TABLE T1 (Codigo INT, Nome VARCHAR(50), CPF CHAR(11))

— Insere propositalmente registros duplicados
INSERT INTO T1 VALUES (10,‘Gilson Ferreira’,‘79934156709’)
INSERT INTO T1 VALUES (10,‘Gilson Ferreira’,‘79934156709’)

INSERT INTO T1 VALUES (20,‘Daniel Rodarte’,‘68154366812’)
INSERT INTO T1 VALUES (20,‘Daniel Rodarte’,‘68154366812’)
INSERT INTO T1 VALUES (20,‘Daniel Rodarte’,‘68154366812’)

INSERT INTO T1 VALUES (30,‘Elias Gomes Frota’,‘80120394011’)

O primeiro passo é identificar os registros duplicados (normalmente através de uma chave primária) para posteriormente excluí-los.

SELECT Codigo, COUNT(*) As Repeticoes FROM T1
GROUP BY Codigo

A consulta mostra que há 2 duas ocorrências para o código 10, três ocorrências para o código 20 e uma ocorrência para o código 30 (o único que não possui repetições). A forma mais trivial de eliminar as repetições é gravar as combinações únicas para uma tabela a parte, remover o conteúdo da tabela original e depois copiar novamente o conteúdo. Ex:

— Grava os dados em uma tabela temporária
SELECT DISTINCT Codigo, Nome, CPF INTO #T1 FROM T1

— Exclui os registros da tabela original
DELETE FROM T1

— Insere os registros da temporária para a tabela original
INSERT INTO T1 (Codigo, Nome, CPF)
SELECT Codigo, Nome, CPF FROM #T1

— Exclui a tabela temporária
DROP TABLE #T1

Situação 2 – Única tabela com apenas a chave primária repetida

Na situação anterior, é muito fácil tratar as repetições já que todos os campos são repetidos. Nem sempre quando existem linhas duplicadas as questões são tão fáceis. Pode acontecer da chave primária repetir-se mas talvez os demais campos não sejam todos repetidos e nesse caso o uso do DISTINCT é ineficaz. Ex:

— Cria uma tabela
CREATE TABLE T2 (
    Codigo INT, Nome VARCHAR(50),
    CPF CHAR(11), DataCadastro SMALLDATETIME)

— Insere propositalmente registros duplicados (com o nome não duplicado)
INSERT INTO T2 VALUES (10,‘Gilson Ferreira’,‘79934156709’, ‘20071217’)
INSERT INTO T2 VALUES (10,‘Fernando Cunha’,‘79934156709’, ‘20080520’)

INSERT INTO T2 VALUES (20,‘Daniel Fernandes’,‘68154366812’,‘20080915’)
INSERT INTO T2 VALUES (20,‘Daniel Rodarte’,‘68154366812’,‘20081019’)
INSERT INTO T2 VALUES (20,‘Daniel R. Fernandes’,‘68154366812’,‘20090328’)

INSERT INTO T2 VALUES (30,‘Elias Gomes Frota’,‘80120394011’,‘20090401’)

Se o DISTINCT for utilizado (conforme a solução anterior) ele não não funcionará. Embora o código 10 tenha o mesmo CPF para ambos os registros, o nome e a data de cadastro são diferentes. O DISTINCT não iria eliminar a repetição, pois, a mesma está caracterizada apenas pelo código (e coincidentemente o CPF) enquanto que o nome e a data de cadastro não estão repetidas.

Ainda que deva haver apenas um único registro por código, como os registros duplicados não são exatamente os mesmos é necessário escolher um critério de desempate (um tiebreaker) para decidir qual registro irá ficar e quais serão eliminados. Pode-se escolher com base na data de cadastro mais recente ou a mais antiga. Pode-se escolher com base no nome maior ou com base no mais curto. Pode-se ainda escolher aleatoriamente. Utilizarei a data de cadastro mais recente.

SELECT CODIGO, NOME, CPF, MAX(DataCadastro) As DataRecente FROM T2
GROUP BY CODIGO, NOME, CPF

O código abaixo parece retornar os códigos com a data de cadastro mais recente (MAX na coluna Data) e embora seja uma tentativa comum, na verdade ele não faz isso. Ele não é capaz de retirar as repetições, pois, para o conjunto CODIGO, NOME e CPF haverá sempre uma única data. É necessário exibir os dados da última data sem agrupar por nome. Uma tentativa é retirar a coluna Nome do GROUP BY já que o CPF por coincidência é repetido. Se isso for feito as repetições são eliminadas mas a coluna Nome também não é exibida. Para que se possa exibir o campo nome relacionado à última data é necessário um pouco mais de código (detalho esse código no artigo "Como retornar o último registro por grupo ?")

;WITH Registros As (
    SELECT CODIGO, MAX(DataCadastro) As DataRecente
    FROM T2 GROUP BY CODIGO)

SELECT * FROM T2
WHERE EXISTS (
    SELECT * FROM Registros As R
    WHERE
        T2.Codigo = R.Codigo AND
        T2.DataCadastro = R.DataRecente)

Esse código retorna exatamente os registros os quais para cada código está presente apenas a última data de cadastro, ou seja, a data mais recente (se fosse necessário a data mais antiga, bastaria substituir o MAX por MIN). Se esses são registros que devem permanecer, basta apenas efetuar a exclusão dos demais registros que não estejam contemplados nesse conjunto.

;WITH Registros As (
    SELECT CODIGO, MAX(DataCadastro) As DataRecente
    FROM T2 GROUP BY CODIGO)

— Exclui todos os registros que não estejam no critério de desempate
DELETE FROM T2
WHERE NOT EXISTS (
    SELECT* FROM Registros As R
    WHERE
        T2.Codigo = R.Codigo AND
        T2.DataCadastro = R.DataRecente)

— Verifica os registros da tabela
SELECT * FROM T2

O uso do DELETE nesse caso pode ser interessante, pois, não foi necessário transportar os registros para uma tabela temporária e depois devolvê-los (existem algumas situações em que isso não é possível). O critério utilizado foi o da data mais recente.

Situação 3 – Um tabela pai com todos os campos repetidos e a presença de tabelas filhas

É nesse ponto que os scripts padrão para eliminar linhas duplicadas falham. A maioria deles ignora que a tabela que possui os registros duplicados tem relacionamentos. Em um banco de dados relacional é de se esperar que as tabelas estejam ligadas e certamente a Lei de Murphy fará com que a tabela que tem os registros duplicados seja justamente aquela que tem milhares de relacionamentos. Nesse caso, não é possível simplesmente mover o conteúdo para uma tabela temporária e nem deletar os registros diretamente, pois, violações de FK podem ocorrer.

— Cria uma tabela
CREATE TABLE T3 (Codigo INT PRIMARY KEY,
    Nome VARCHAR(50), CPF CHAR(11))

— Insere propositalmente registros duplicados
— Por razões óbvias a chave não é duplicada
INSERT INTO T3 VALUES (1,‘Gilson Ferreira’,‘79934156709’)
INSERT INTO T3 VALUES (2,‘Gilson Ferreira’,‘79934156709’)

INSERT INTO T3 VALUES (3,‘Daniel Rodarte’,‘68154366812’)
INSERT INTO T3 VALUES (4,‘Daniel Rodarte’,‘68154366812’)
INSERT INTO T3 VALUES (5,‘Daniel Rodarte’,‘68154366812’)

INSERT INTO T3 VALUES (6,‘Elias Gomes Frota’,‘80120394011’)

INSERT INTO T3 VALUES (7,‘Frederico Martins’,‘70544321220’)

— Cria uma tabela filha
CREATE TABLE T4 (CodigoPK INT PRIMARY KEY,
    CodigoFK INT FOREIGN KEY REFERENCES T3 (Codigo))

— Insere alguns registros na tabela filha
INSERT INTO T4 VALUES (1,1)
INSERT INTO T4 VALUES (2,1)
INSERT INTO T4 VALUES (3,2)
INSERT INTO T4 VALUES (4,3)
INSERT INTO T4 VALUES (5,3)
INSERT INTO T4 VALUES (6,4)
INSERT INTO T4 VALUES (7,5)
INSERT INTO T4 VALUES (8,6)
INSERT INTO T4 VALUES (9,6)

Nenhuma das soluções apresentadas até então resolve o problema. Em virtude dos relacionamentos não é possível excluir os registros para uma tabela temporária com o uso do DISTINCT e depois retorná-los a tabela original. Também não é possível excluir registros sobre nenhum critérios, pois, quase todos os registros possuem ocorrências com a tabela filha (com exceção de Frederico)

O primeiro passo para resolver uma situação desse tipo é identificar qual o critério de desempate (tiebreaker). Todas as colunas são iguais e sendo o Identity uma chave artificial, ou seja, sem significado para o negócio é possível utilizá-la como tiebreaker. Pode-se optar por deixar os registros com o menor ID ou com o maior ID (semelhante a situação 2). Essa é uma abordagem interessante também, mas optarei por escolher os IDs de forma aleatória.

;WITH Combinacoes As (
    SELECT DISTINCT Nome, CPF
    FROM T3 As TOut)

SELECT Nome, CPF,
    (SELECT TOP 1 Codigo FROM T3 As TInt
    WHERE TOut.Nome = TInt.Nome And TOut.CPF = TInt.CPF
    ORDER BY NewID()) As CodigoEscolhido
FROM Combinacoes As TOut

Se esse trecho de codigo for executado, os códigos de Gilson e Daniel irão ser escolhidos aleatoriamente. A cada execução um código de seus registros duplicados será escolhido. Elias e Frederico não possuem nenhum valor diferente de 6 e 7, pois, como não possuem registros duplicados, mesmo uma escolha aleatória irá capturar sempre os seus códigos que são únicos.

Agora que os códigos escolhidos estão definidos é preciso atualizar os registros na tabela filha. Enquanto as atualizações na tabela filha não forem realizadas, não será possível excluir registros duplicados na tabela pai. Antes de prosseguir com o comando de UPDATE é preciso fazer um mapeamento de T3 com todos os códigos escolhidos.

;WITH Combinacoes As (
    SELECT DISTINCT Nome, CPF
    FROM T3 As TOut),

CodigosEscolhidos As (

SELECT Nome, CPF,
    (SELECT TOP 1 Codigo FROM T3 As TInt
    WHERE TOut.Nome = TInt.Nome And TOut.CPF = TInt.CPF
    ORDER BY NewID()) As CodigoEscolhido
FROM Combinacoes As TOut)

SELECT T3.*, CodigoEscolhido
FROM T3
INNER JOIN CodigosEscolhidos As CE
    ON T3.Nome = CE.Nome And T3.CPF = CE.CPF
ORDER BY T3.Codigo

Os registros são aleatórios, mas no meu caso obtive o seguinte retorno:

Codigo	Nome	CPF	CodigoEscolhido
1	Gilson Ferreira	79934156709	2
2	Gilson Ferreira	79934156709	2
3	Daniel Rodarte	68154366812	5
4	Daniel Rodarte	68154366812	5
5	Daniel Rodarte	68154366812	5
6	Elias Gomes Frota	80120394011	6
7	Frederico Martins	70544321220	7

Agora que há um mapeamento entre a coluna Codigo e a coluna CodigoEscolhido, o próximo passo é atualizar a tabela filha para que as referências na coluna CodigoFK sejam refletidas para os correspondentes em CodigoEscolhido. Em virtude da escolha ser aleatória, é possível que o resultado da atualização não reflita exatamente a tabela que postei agora a pouco.

;WITH Combinacoes As (
    SELECT DISTINCT Nome, CPF
    FROM T3 As TOut),

CodigosEscolhidos As (

SELECT Nome, CPF,
    (SELECT TOP 1 Codigo FROM T3 As TInt
    WHERE TOut.Nome = TInt.Nome And TOut.CPF = TInt.CPF
    ORDER BY NewID()) As CodigoEscolhido
FROM Combinacoes As TOut),

CodigosMapeados As (

SELECT T3.*, CodigoEscolhido
    FROM T3
    INNER JOIN CodigosEscolhidos As CE
        ON T3.Nome = CE.Nome And T3.CPF = CE.CPF)

UPDATE T4 SET
    CodigoFK = CodigoEscolhido
OUTPUT DELETED.*, INSERTED.*
FROM T4
INNER JOIN CodigosMapeados As CM
    ON T4.CodigoFK = CM.Codigo

O código acima escolhe algum código de forma aleatória para as combinações únicas baseadas em Nome e CPF e atualiza a tabela filha com base nos códigos aleatórios escolhidos. A cláusula DELETED e INSERTED utilizadas no UPDATE servem apenas para retornar a situação da tabela filha antes e após a execução do comando conforme mostra a tabela abaixo:

Antes		Depois
CodigoPK	CodigoFK	CodigoPK	CodigoFK
4	3	4	4
5	3	5	4
6	4	6	4
7	5	7	4
8	6	8	6
9	6	9	6
2	1	2	2
3	1	3	2
3	2	3	2

Após a execução do script, é possível visualizar que os códigos 3, 4 e 5 na coluna CodigoFK foram mapeados para 4 (código escolhido para Daniel) e que os códigos 1 e 2 foram mapeados para 2 (código escolhido para Gilson). Como Elias não possui registros duplicados, o código 6 continua mapeado para ele mesmo. A tabela pode ter os resultados variáveis em virtude do código ser aleatório, mas todos os códigos terão o seu mapeamento (talvez 3, 4 e 5 mapeiem para 3 ou para 5 ou 1 e 2 mapeiem para 1).

Agora que a tabela filha está com as referências apontadas para o código escolhido na tabela pai, fica fácil prosseguir com atualização. Basta excluir os registros da tabela pai que sejam duplicados e que não tem equivalentes na tabela filha. As CTEs abaixo podem identificá-los:

;WITH Duplicados As (

    SELECT Nome, CPF FROM T3
    GROUP BY Nome, CPF
    HAVING COUNT(*) > 1),

CodigosExcluidos As (

    SELECT T3.* FROM T3
    INNER JOIN Duplicados As D
        ON T3.Nome = D.Nome And T3.CPF = D.CPF
    WHERE NOT EXISTS
        (SELECT * FROM T4 WHERE T3.Codigo = T4.CodigoFK))

SELECT * FROM CodigosExcluidos

De posse dos códigos o próximo passo é excluí-los da tabela pai.

;WITH Duplicados As (

    SELECT Nome, CPF FROM T3
    GROUP BY Nome, CPF
    HAVING COUNT(*) > 1),

CodigosExcluidos As (

    SELECT T3.* FROM T3
    INNER JOIN Duplicados As D
        ON T3.Nome = D.Nome And T3.CPF = D.CPF
    WHERE NOT EXISTS
        (SELECT * FROM T4 WHERE T3.Codigo = T4.CodigoFK))

SELECT * FROM CodigosExcluidos

DELETE FROM T3
WHERE EXISTS (
    SELECT * FROM CodigosExcluidos As CE
    WHERE T3.Codigo = CE.Codigo)

— Verifica se a exclusão foi realizada
SELECT * FROM T3

E o resultado com os registros sobreviventes e sem linhas duplicadas é exibido abaixo:

Codigo	Nome	CPF	CodigoEscolhido
2	Gilson Ferreira	79934156709	2
3	Daniel Rodarte	68154366812	5
6	Elias Gomes Frota	80120394011	6
7	Frederico Martins	70544321220	7

Alguns poderiam perguntar se após a atualização das tabelas filhas com o mapeamento dos códigos não seria mais fácil excluir os registros da tabela pai que não tivessem correspondentes na tabela filha. Pode parecer intuitivo, mas se esse comando fosse feito, junto com os registros duplicados, o registro com o código 7 (Frederico) também seria excluído (mesmo sem ter correspondentes na tabela filha).

E se houvesse mais tabelas filhas relacionadas ? Nesse caso um processo similar teria de ser feito para atualizar cada tabela filha e posteriormente efetuar as exclusões. Em uma situação dessas é preciso escolher um critério fixo (o maior ID ou quem sabe o menor ID) ou persistir os resultados aleatórios. Se isso não for feito é possível que cada tabela filha tenha uma combinação de códigos aleatórios diferentes. Para descobrir quem são as tabelas filhas, recomendo o artigo "Mapeando dependências entre tabelas".

As maneiras apresentadas não são as únicas formas de eliminar registros duplicados. É possível utilizar as funções novas do SQL Server como o Row_Number para criar critérios de desempate (tiebreaker) ou no caso das versões anteriores recorrer ao Identity.

Aqueles que não possuírem o SQL Server 2005 ou superior à disposição podem utilizar um artigo da Microsoft que trata sobre linhas duplicadas. Como eu havia dito, no assunto relacionamentos, boa parte desses artigos fica devendo mas segue o link:

How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444/en-us

Linhas duplicadas à primeira vista podem parecer um problema bem simples de resolver. De fato visualmente é algo bem simples (basta trocar esse por aquele), mas quando há muito registros e muitas tabelas envolvidas algumas coisas não são tão simples assim. Vale a pena lembrar que eliminar as repetições é uma abordagem reativa, pois, não garante que no futuro as linhas duplicadas não irão existir. É necessário sempre que os modelos de dados, as aplicações e o banco de dados possam garantir que linhas duplicadas não possam ser inseridas seja através de validações na aplicação e (ou) através das constraints. Isso é necessário para garantir a qualidade do dados que são armazenados.

[ ]s,

Gustavo