Supprimer les doublons en SQL Oracle (ROWID, ROW_NUMBER)

Supprimer les doublons en SQL Oracle se fait le plus souvent grâce à la pseudo-colonne ROWID, qui identifie physiquement chaque ligne — même quand deux lignes ont des valeurs identiques. Voici les méthodes éprouvées pour détecter puis supprimer les doublons, sans effacer les originaux.

Étape 1 — Détecter les doublons

SELECT email, COUNT(*)
FROM   clients
GROUP  BY email
HAVING COUNT(*) > 1;

Ici, on considère qu’un doublon = même email. Adaptez la liste de colonnes à votre définition du doublon.

Méthode 1 — ROWID (la plus classique)

On garde, pour chaque groupe de doublons, la ligne au plus petit ROWID et on supprime les autres :

DELETE FROM clients c
WHERE c.ROWID > (
  SELECT MIN(c2.ROWID)
  FROM clients c2
  WHERE c2.email = c.email      -- même définition du doublon
);
COMMIT;

Chaque ligne dont le ROWID n’est pas le minimum de son groupe est supprimée. C’est la méthode universelle, sans clé technique.

Méthode 2 — ROW_NUMBER (lisible, moderne)

DELETE FROM clients
WHERE ROWID IN (
  SELECT rid FROM (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM clients
  )
  WHERE rn > 1
);
COMMIT;

ROW_NUMBER numérote les doublons par groupe ; on supprime tout ce qui a rn > 1. Le ORDER BY décide quelle ligne est conservée (ici, le plus petit id).

Méthode 3 — Recréer la table (gros volumes)

Pour des millions de lignes, supprimer ligne à ligne est lent. Plus rapide : recréer une table dédoublonnée.

CREATE TABLE clients_clean AS
SELECT * FROM clients c
WHERE c.ROWID = (SELECT MIN(c2.ROWID) FROM clients c2 WHERE c2.email = c.email);

-- puis remplacer (après vérification !)
-- DROP TABLE clients; RENAME clients_clean TO clients;

Empêcher les futurs doublons

Une fois nettoyé, ajoutez une contrainte d’unicité pour bloquer les insertions en double (qui déclencheront alors ORA-00001) :

ALTER TABLE clients ADD CONSTRAINT uq_clients_email UNIQUE (email);

Afficher des résultats SANS doublons (sans supprimer)

Si vous voulez juste une lecture dédoublonnée, pas besoin de DELETE :

SELECT DISTINCT email, nom FROM clients;

FAQ

Qu’est-ce que ROWID ?

C’est l’adresse physique unique d’une ligne dans Oracle. Même deux lignes parfaitement identiques ont des ROWID différents — c’est ce qui permet de les distinguer pour supprimer les doublons.

Comment choisir quelle ligne garder ?

Avec ROW_NUMBER, le ORDER BY décide (ex. garder la plus récente : ORDER BY date_creation DESC). Avec ROWID, on garde le MIN(ROWID).

Faut-il faire un COMMIT ?

Oui, le DELETE est transactionnel : validez avec COMMIT (ou annulez avec ROLLBACK avant validation). Faites toujours une sauvegarde avant un gros nettoyage.