Como remover linhas duplicadas de uma tabela no SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 139444 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Sumário

Tabelas do Microsoft SQL Server nunca devem conter linhas duplicadas nem chaves primárias não-exclusivos. Para questões de brevidade, às vezes referirá a chaves primárias como "chave" ou "CP" neste artigo, mas isso irá sempre indicar "chave primária." PKs duplicados são uma violação de integridade de entidade e devem ser permitidos em um sistema relacional. SQL Server possui vários mecanismos para impor integridade de entidade, incluindo índices, restrições UNIQUE, restrições PRIMARY KEY e disparadores.

Apesar de isso, duplicadas chaves primárias podem ocorrer em circunstâncias incomuns e, caso deve ser eliminados. Uma maneira que podem ocorrer é se PKs duplicados existem nos dados não-relacionais fora do SQL Server e os dados são importados enquanto CP exclusividade não está sendo aplicada. Outra maneira de que eles podem ocorrer é por meio de um erro de design de banco de dados, como não impor integridade de entidade em cada tabela.

Com freqüência PKs duplicados são percebidos ao tentar criar um índice exclusivo, que será finalizado se chaves duplicadas forem encontradas. Essa mensagem é:
Msg 1505, nível 16, estado 1 crie índice exclusivo interrompido na chave duplicada.
Se você estiver usando o SQL Server 2000 ou SQL Server 2005, você receberá a seguinte mensagem de erro:
Msg 1505, nível 16, estado 1 CREATE UNIQUE INDEX finalizado porque uma chave duplicada foi encontrada para nome do objeto ' %. * ls 'e nome de índice' %. * ls'. O valor de chave duplicado é % 1!.
Este artigo descreve como localizar e remover chaves primárias duplicadas de uma tabela. No entanto, você deve examinar detalhadamente o processo que permitia as acontecer para impedir que uma recorrência de duplicatas.

Mais Informações

Neste exemplo, usaremos a tabela a seguir com valores duplicados do CP. Nesta tabela, a chave primária é de duas colunas (col1, col2). Nós não pode criar um índice exclusivo ou a restrição PRIMARY KEY como duas linhas têm PKs duplicados. Este procedimento ilustra como identificar e remover as duplicatas.
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
				
a primeira etapa é identificar quais linhas têm valores de chave primários duplicados:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
				
este retornará uma linha para cada conjunto de valores de CP duplicados na tabela. A última coluna esse resultado é o número de duplicatas para determinado valor CP.

Recolher esta tabelaExpandir esta tabela
Col1Col2
112


Se houver apenas alguns conjuntos de valores CP duplicados, o melhor procedimento é para excluí-las manualmente em uma base individual. Por exemplo:
set rowcount 1
delete from t1
where col1=1 and col2=1
				
o valor de número de linhas deve ser o número de duplicatas para um determinado valor chave n-1. Nesse exemplo, há 2 duplicatas para que o número de linhas é definido como 1. Os valores de col1/col2 são tirados do resultado da consulta GROUP BY acima. Se a consulta GROUP BY retorna várias linhas, a consulta "set rowcount" terá a ser executado uma vez para cada uma das seguintes linhas. Cada vez que ele é executado, definidas rowcount para o número de duplicatas de determinado valor CP n-1.

Antes de excluir as linhas, você deve verificar que a linha inteira é duplicada. Embora improvável, é possível que os valores de CP são duplicados, mas a linha como um todo não é. Um exemplo disso seria uma tabela com número da previdência social como chave primária, e cada um com dois diferentes pessoas (ou linhas) com o mesmo número, ter atributos exclusivos. Nesse caso qualquer mau funcionamento causado chave duplicada pode ter também causado válidos exclusivos dados sejam colocados na linha. Esses dados devem ser copiados e preservados para reconciliação de estudo e possível anteriores à exclusão de dados.

Se houver muitos conjuntos distintos de valores de CP duplicados na tabela, pode ser muito demorado para removê-los individualmente. Nesse caso o procedimento a seguir pode ser usado:
  1. Primeiro, execute a consulta acima GROUP BY para determinar quantos conjuntos de valores duplicados do CP existem e a contagem de duplicatas para cada conjunto.
  2. Selecione os valores chaves duplicados em uma tabela de controladora. Por exemplo:
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
    					
  3. Selecione as linhas duplicadas em uma tabela controladora, eliminando duplicatas no processo. Por exemplo:
    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  4. Neste ponto, a tabela holddups deve ter PKs exclusivos, no entanto, isso não pode ser o caso se t1 tivesse PKs duplicados, mas linhas únicas (como no exemplo SSN acima). Verifique se cada chave em holddups é exclusivo e que você não tem chaves duplicadas, mas linhas únicas. Se assim, você deve parar aqui e reconciliar quais as linhas que deseja manter para um determinado valor de chave duplicado. Por exemplo, a consulta:
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    deve retornar uma contagem de 1 para cada linha. Em caso afirmativo, vá para a etapa 5 abaixo. Se não, você tem chaves duplicadas, mas linhas únicas e precisa decidir quais linhas deseja salvar. Isso geralmente envolvem o descartando uma linha ou criar um novo valor de chave exclusivo para esta linha. Execute uma dessas duas etapas para cada tal CP duplicado na tabela holddups.
  5. Exclua linhas duplicadas da tabela original. Por exemplo:
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Colocar as linhas exclusivas volta na tabela original. Por exemplo:
    INSERT t1 SELECT * FROM holddups
    					

Propriedades

ID do artigo: 139444 - Última revisão: quarta-feira, 23 de novembro de 2005 - Revisão: 4.2
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palavras-chave: 
kbmt kbinfo kbusage KB139444 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 139444

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com