Suppression des lignes en double d'une table dans Oracle

Question

Je suis en train de tester quelque chose dans Oracle et j'ai rempli une table avec quelques données échantillons, mais au cours du processus, j'ai accidentellement chargé des enregistrements en double, de sorte que je ne peux pas créer de clé primaire en utilisant certaines des colonnes.

Comment puis-je supprimer toutes les lignes en double et n'en laisser qu'une seule ?

Answer 1

Utilisation de rowid-

delete from emp
 where rowid not in
 (select max(rowid) from emp group by empno);

Utilisation de l'auto-jointure-

delete from emp e1
 where rowid not in
 (select max(rowid) from emp e2
 where e1.empno = e2.empno );

Answer 2

Solution 4)

 delete from emp where rowid in
            (
             select rid from
                (
                  select rowid rid,
                  dense_rank() over(partition by empno order by rowid
                ) rn
             from emp
            )
 where rn > 1
);

Answer 3

1. solution

delete from emp
    where rowid not in
    (select max(rowid) from emp group by empno);

2. sloution

delete from emp where rowid in
               (
                 select rid from
                  (
                    select rowid rid,
                      row_number() over(partition by empno order by empno) rn
                      from emp
                  )
                where rn > 1
               );

3.solution

delete from emp e1
         where rowid not in
          (select max(rowid) from emp e2
           where e1.empno = e2.empno ); 

4. solution

 delete from emp where rowid in
            (
             select rid from
                (
                  select rowid rid,
                  dense_rank() over(partition by empno order by rowid
                ) rn
             from emp
            )
 where rn > 1
);

Answer 4

5. solution

delete from emp where rowid in 
    (
      select  rid from
       (
         select rowid rid,rank() over (partition by emp_id order by rowid)rn from emp     
       )
     where rn > 1
    );

Answer 5

DELETE from table_name where rowid not in (select min(rowid) FROM table_name group by column_name);

et vous pouvez également supprimer les enregistrements en double d'une autre manière

DELETE from table_name a where rowid > (select min(rowid) FROM table_name b where a.column=b.column);