Como remover linhas duplicadas de uma tabela no SQL Server

Traduções de Artigos Traduções de Artigos
Artigo: 139444 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Sumário

Tabelas do Microsoft SQL Server nunca devem conter linhas duplicadas, nem as chaves primárias não exclusivas. Para brevity, irá por vezes referidas chaves primárias como "key" ou "PK" neste artigo, mas esta será sempre indicar "chave primária." PKs duplicados são uma violação de integridade de entidade e devem ser permitidos num sistema relacional. O SQL Server tem vários mecanismos para impor a integridade da entidade, incluindo índices, restrições UNIQUE, PRIMARY KEY restrições e accionadores.

Apesar de, em circunstâncias pouco habituais, poderão ocorrer duplicadas chaves primárias e, caso que devem ser eliminadas. Uma forma que podem ocorrer é se existem PKs duplicados no não relacionais dados fora do SQL Server e importados os dados enquanto não está a ser imposta PK exclusividade. Outra forma que podem ocorrer é através de um erro de estrutura da base de dados, tais como não impor integridade de entidade em cada tabela.

Muitas vezes PKs duplicados são reparados quando tenta criar um índice exclusivo, que vai abortar se forem encontradas chaves duplicadas. Esta mensagem é:
Msg 1505, 16 de nível 1 estado crie índice exclusivo abortado na chave duplicado.
Se estiver a utilizar o SQL Server 2000 ou SQL Server 2005, poderá receber a seguinte mensagem de erro:
Msg 1505, 16 de nível de estado 1 CREATE UNIQUE INDEX terminada porque foi encontrada uma chave duplicada para nome do objecto ' %. * ls % 'e nome de índice'. * 'ls. O valor de chave duplicado é % ls.
Este artigo descreve como localizar e remover chaves primárias duplicadas de uma tabela. No entanto, deve examinar melhor o processo que permitido duplicados ocorrer de modo a impedir uma periodicidade.

Mais Informação

Neste exemplo, utilizaremos a tabela seguinte com valores PK duplicados. Nesta tabela de chave primária é duas colunas (col1, col2). -Não é possível criar um índice exclusivo ou restrição PRIMARY KEY uma vez que duas linhas têm PKs duplicados. Este procedimento ilustra a identificar e remover os duplicados.
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')
				
o primeiro passo consiste em identificar as linhas têm valores de chave primários duplicados:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
				
Isto irá devolver uma linha para cada conjunto de valores PK duplicados na tabela. A última coluna neste resultado é o número de duplicados para o valor PK específico.

Reduzir esta tabelaExpandir esta tabela
Col1Col2
112


Se existirem apenas alguns conjuntos de valores PK duplicados, o melhor procedimento é para eliminá-las manualmente numa base individual. Por exemplo:
set rowcount 1
delete from t1
where col1=1 and col2=1
				
o valor de rowcount deve ser o número de duplicados para um determinado valor de chave n-1. Neste exemplo, existem 2 duplicados para que rowcount estiver definido como 1. Os valores de col1/col2 são retirados da acima GROUP BY resultado da consulta. Se a consulta GROUP BY devolver múltiplas linhas, a consulta "Definir rowcount" tem de ser executada uma vez para cada um destas linhas. Sempre que é executada, defina rowcount para o número de duplicados de determinado valor de PK n-1.

Antes de eliminar as linhas, deve verificar que a linha inteira é duplicada. Embora improvável, é possível que os valores de PK são duplicados, mas a linha como um todo não é. Um exemplo desta situação seria uma tabela com o número da segurança social como chave primária, e ter duas diferentes pessoas (ou linhas) com o mesmo número, cada um com atributos únicos. Neste caso o mau funcionamento provocado chave duplicada pode ter também causado válidos dados exclusivos seja colocada na linha. Estes dados devem ser copiados e mantém para reconciliação estudo e possível antes para eliminar os dados.

Se existirem muitos conjuntos distintos de valores PK duplicados na tabela, poderá demorar demasiado tempo para removê-los individualmente. Neste caso pode ser utilizado o seguinte procedimento:
  1. Em primeiro lugar, execute a consulta acima GROUP BY para determinar quantos conjuntos de valores duplicados de PK existirem e a contagem de duplicados para cada conjunto.
  2. Seleccione os valores chaves duplicados numa tabela proprietárias. Por exemplo:
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
    					
  3. Seleccione as linhas duplicadas numa tabela proprietárias, eliminando duplicados 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, não será o caso, se tinha t1 PKs duplicados, mas as linhas exclusivas (tal como no exemplo SSN acima). Certifique-se que cada chave holddups é exclusivo e que não tem chaves duplicadas, mas as linhas exclusivas. Se assim, deve parar aqui e reconciliar que as linhas que pretende manter para um determinado valor de chave duplicado. Por exemplo, a consulta:
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    deverá devolver uma contagem de 1 para cada linha. Em caso afirmativo, avance para o passo 5 abaixo. Caso contrário, tem chaves duplicadas, mas as linhas exclusivas e tem de decidir quais as linhas para guardar. Isto normalmente entail ou eliminar uma linha ou criar um novo valor de chave exclusivo para esta linha. Tirar um destes dois passos para cada PK esse duplicados na tabela holddups.
  5. Elimine linhas duplicadas da tabela original. Por exemplo:
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Colocar linhas únicas novamente na tabela original. Por exemplo:
    INSERT t1 SELECT * FROM holddups
    					

Propriedades

Artigo: 139444 - Última revisão: 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 2005 Server Enterprise
  • Microsoft SQL 2005 Server Workgroup
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 revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes 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