Comment faire pour supprimer des lignes en double d'une table dans SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 139444 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Résumé

Les tables Microsoft SQL Server ne doivent jamais contenir des lignes en double, ni de clés primaires non uniques. Pour des raisons de concision dans cet article, nous ferons référence quelquefois aux clés primaires avec le terme « clé », mais cela signifiera toujours « clé primaire ». Les clés primaires en double violent l'intégrité de l'entité et doivent être interdites dans un système relationnel. SQL Server dispose de plusieurs mécanismes pour mettre en ?uvre l'intégrité d'entité, notamment les index, les contraintes UNIQUE, les contraintes PRIMARY KEY et les déclencheurs.

Cependant, dans certaines circonstances des clés primaires en double peuvent exister, et si tel est le cas, elles doivent être éliminées. Des clés primaires en double peuvent exister, si elles existaient dans des données non relationnelles en dehors de SQL Server qui ont été importées sans la mise en ?uvre de l'option d'unicité des clés primaires. De même, en cas d'erreur de conception de la base de données, par exemple la non-mise en ?uvre de l'intégrité des entités dans chaque table, des clés primaires peuvent exister.

Les clés primaires en double sont souvent détectées lors de la création d'un index unique, car ce dernier ne pourra pas être créé si des clés en double sont trouvées. Le message suivant s'affichera :
Msg 1505, Level 16, State 1 Create unique index s'est arrêté parce qu'une clé a été trouvée en double.
Si vous utilisez SQL Server 2000 ou SQL Server 2005, vous pouvez recevoir le message d'erreur suivant :
Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX s'est terminé car une clé dupliquée a été trouvée pour l'objet '%.*ls' et l'index '%.*ls'. Valeur de clé dupliquée  :%ls.
Cet article explique comment faire pour localiser et supprimer des clés primaires en double d'une table. Toutefois, vous devez examiner attentivement le processus qui a permis la création de ces doublons afin que ces doublons ne se reproduisent pas.

Plus d'informations

Pour cet exemple, nous utiliserons la table suivante avec les valeurs de clés primaires en double. Dans cette table, les deux colonnes col1 et col2 représentent la clé primaire. Nous ne pouvons pas créer un index unique ou une contrainte PRIMARY KEY, car deux lignes ont des clés primaires en double. Cette procédure montre comment faire pour identifier et supprimer les doublons.
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')
				
La première étape consiste à identifier les lignes qui ont des valeurs de clé primaire en double :
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
				
Une ligne pour chaque ensemble de valeurs de clé primaire en double dans la table sera retournée. La dernière colonne dans ce résultat est le nombre de doublons pour la valeur de clé primaire spécifique.

Réduire ce tableauAgrandir ce tableau
col1col2
112


S'il existe seulement quelques jeux de valeurs de clé primaire en double, la meilleure procédure consiste à supprimer manuellement ceux-ci un par un. Par exemple :
set rowcount 1
delete from t1
where col1=1 and col2=1
				
La valeur de rowcount doit être n-1 fois le nombre de doublons pour une valeur de clé donnée. Dans cet exemple, il y a 2 doublons, donc le rowcount a la valeur 1. Les valeurs col1/col2 sont extraites du résultat de la requête GROUP BY. Si la requête GROUP BY retourne plusieurs lignes, la requête « set rowcount » devra être exécutée une fois pour chacune de ces lignes. Chaque fois qu'elle est exécutée, définissez le rowcount à n-1 fois le nombre de doublons de la valeur de clé primaire spécifique.

Avant de supprimer les lignes, vous devez vérifier que la ligne entière est en double. Bien qu'improbable, il est possible que les valeurs de clé primaire soient en double, mais pas l'intégralité de la ligne. Par exemple, une table ayant comme clé primaire Numéro de sécurité sociale et deux personnes différentes (ou lignes) avec le même numéro, chacune avec des attributs uniques. Dans ce cas, la cause ayant provoqué la clé en double peut également être à l'origine de données uniques valides contenues dans la ligne. Ces données doivent être copiées et étudiées et une réconciliation effectuée avant de supprimer les données.

Si de nombreux jeux de valeurs de clé primaire distincts existent dans la table, leur suppression individuelle peut prendre trop de temps. Dans ce cas, utilisez la procédure suivante :
  1. Tout d'abord, exécutez la requête GROUP BY pour déterminer le nombre de jeux de valeurs de clé primaire en double et le nombre de doublons pour chaque jeu.
  2. Sélectionnez les valeurs de clé primaire en double et copiez-les dans une table créée pour contenir ces données. Par exemple :
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
    					
  3. Sélectionnez les lignes en double et copiez-les dans une table créée pour contenir ces données, tout en éliminant les doublons dans le même temps. Par exemple :
    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  4. À ce stade, la table holddups doit contenir des clés primaires uniques, toutefois, ce ne sera pas le cas si t1 avait des clés en double, et des lignes uniques (comme dans l'exemple SSN ci-dessus). Vérifiez que chaque clé dans la table holddups est unique, que vous n'avez pas de clés en double et des lignes uniques. Si tel est le cas, vous devez vous arrêter à ce stade et réconcilier les lignes à conserver pour une valeur de clé en double donnée. Par exemple, la requête :
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    doit retourner un nombre de 1 pour chaque ligne. Si tel est le cas, passez à l'étape 5 ci-dessous. Dans le cas contraire, vous avez des clés en double, mais des lignes uniques et vous devez décider quelles lignes enregistrer. Vous devez alors ignorer une ligne ou créer une nouvelle valeur de clé unique pour cette ligne. Choisissez l'une de ces deux méthodes pour chaque clé primaire en double dans la table holddups.
  5. Supprimez les lignes en double de la table d'origine. Par exemple :
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Insérez de nouveau les lignes uniques dans la table d'origine. Par exemple :
    INSERT t1 SELECT * FROM holddups
    					

Propriétés

Numéro d'article: 139444 - Dernière mise à jour: vendredi 18 février 2011 - Version: 5.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 6.0 Standard
  • Microsoft SQL Server 6.5 Édition Standard
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Standard
  • 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
Mots-clés : 
kbsqlserverengine kbinfo kbusage KB139444
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

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