Supprimer les lignes dupliquées d’une table SQL Server à l’aide d’un script

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 70956

Résumé

Cet article montre comment supprimer des lignes dupliquées d’une table dans Microsoft SQL Server à l’aide d’un script Transact-SQL (T-SQL). Il décrit deux méthodes courantes : la copie d’une instance de chaque ligne dupliquée dans une table distincte et l’utilisation de la ROW_NUMBER fonction pour supprimer les lignes supplémentaires en place. Utilisez cet article si une table contient des enregistrements répétés, par exemple parce qu’elle n’a pas de contrainte unique ou de clé primaire. La méthode 2 est plus simple, car elle fonctionne en place.

Créer un exemple de table qui contient des lignes dupliquées

Pour illustrer les deux méthodes, créez un exemple de table nommé original_table qui comporte une colonne, key_value, et ajoutez des données en double :

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Utilisez ensuite l’une des méthodes suivantes pour supprimer les lignes dupliquées de la table.

Note

Ces scripts suppriment définitivement les données. Sauvegardez la table ou la base de données avant toute exécution sur des données de production.

Méthode 1 : Déplacer des lignes dupliquées vers une table distincte et réinsérer une seule copie

La méthode 1 utilise deux tables : original_table et une deuxième table, duplicate_tableque le script crée, puis supprime. Pour appliquer le script à une autre table, remplacez original_table par le nom de la table. Cette méthode nécessite la réécriture de la logique GROUP BY et COUNT si un doublon est défini par plusieurs colonnes.

Exécutez le script suivant :

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Ce script effectue, dans l’ordre, les opérations suivantes :

  • Déplace n’importe quelle ligne en double de la table d’origine vers une table en double.
  • Suppression de toutes les lignes de la table d’origine qui se trouvent également dans la table dupliquée.
  • Déplace les lignes de la table dupliquée vers la table d’origine.
  • Supprime la table en double.

Cette méthode est simple. Toutefois, vous devez disposer d’un espace disponible suffisant dans la base de données pour créer temporairement la table dupliquée. Cette méthode entraîne également une surcharge, car vous déplacez les données.

En outre, si votre table a une colonne IDENTITY , vous devez utiliser SET IDENTITY_INSERT ON lorsque vous restaurez les données dans la table d’origine.

Méthode 2 : Utiliser la fonction ROW_NUMBER pour supprimer les lignes dupliquées

La fonction ROW_NUMBER, qui a été introduite dans Microsoft SQL Server 2005, rend cette opération beaucoup plus simple. Remplacez key_value par la colonne ou les colonnes qui définissent un doublon.

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

Ce script effectue, dans l’ordre, les opérations suivantes :

  • Utilise la fonction ROW_NUMBER pour partitionner les données en fonction de key_value, qui peut correspondre à une ou plusieurs colonnes séparées par des virgules.
  • Supprime tous les enregistrements qui ont reçu une valeur DupRank supérieure à 1. Cette valeur indique que les enregistrements sont des doublons.

En raison de l’expression (SELECT NULL), le script ne trie pas les données partitionnées selon aucune condition. Si votre logique de suppression des doublons nécessite de choisir les enregistrements à supprimer et ceux à conserver en fonction de l’ordre de tri des autres colonnes, vous pouvez utiliser l’expression ORDER BY pour effectuer cette opération.

Conserver la ligne la plus récente lorsque vous supprimez des doublons

Pour conserver la ligne avec la valeur la plus récente, triez dans l’ordre décroissant par cette colonne. Par exemple, considérez un employee_table hypothétique qui a plusieurs lignes pour le même employé. Le script suivant conserve uniquement la ligne qui a la valeur la plus récente updated_date :

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

La ligne qui a la valeur updated_date la plus récente reçoit une valeur DupRank de 1, de sorte que le script la conserve et supprime les lignes plus anciennes.

La méthode 2 est simple et efficace pour ces raisons :

  • Il ne nécessite pas de votre part de copier temporairement les enregistrements dupliqués dans une autre table.
  • Il ne vous oblige pas à joindre la table d’origine avec elle-même (par exemple, en utilisant une sous-requête qui retourne tous les enregistrements dupliqués à l’aide d’une combinaison de GROUP BY et HAVING).
  • Pour des performances optimales, vous devez avoir un index correspondant sur la table qui utilise key_value comme clé d’index et inclut toutes les colonnes de tri que vous avez peut-être utilisées dans l’expression ORDER BY.

La ROW_NUMBER fonction est disponible dans SQL Server 2005 et versions ultérieures. Si vous utilisez une version de SQL Server qui ne prend pas en charge la fonction ROW_NUMBER, utilisez la méthode 1 ou une méthode similaire à la place.

Comment empêcher les lignes dupliquées dans une table ?

Après avoir supprimé les doublons, vous pouvez empêcher de nouvelles lignes dupliquées en définissant une contrainte unique ou un index unique sur la colonne ou les colonnes qui identifient une ligne.

Contenu connexe

  • Dernière mise à jour :