Doublons dans ma vie

SQL> select count(1) from ga_faits where annee='2007' and mois='12';

COUNT(1)
----------
33448

SQL> select count(1) from (select pernr, count(1) from ga_faits where annee='2007' and mois='12' group by pernr having count(1) >1);

COUNT(1)
----------
16724

SQL> select rownum, pernr, annee,mois from ga_faits where annee='2007' and mois='12' and pernr='10015098';

ROWNUM PERNR ANNEE MOIS
---------- ---------- -------- --------
1 10015098 2007 12
2 10015098 2007 12

SQL> delete from ga_faits where pernr='10009309' and annee='2007' and mois='12' and rownum<2;> rollback;

Rollback complete.

SQL> @c:\deleteduplicates.sql

1 row deleted.


1 row deleted.

::::::


1 row deleted.

SQL> select count(1) from ga_faits where annee='2007' and mois='12';

COUNT(1)
----------
16724

SQL> commit;

Commit complete.

SQL> spool off

A2 sao os ids extraidos via spool
criado com excel com a formula ="delete from ga_faits where pernr='"&A2&"' and annee='2007' and mois='12' and rownum<2;"

deleteduplicates.sql parcialmente mostrado

delete from ga_faits where pernr='10009309' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10015098' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10015879' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10032005' and annee='2007' and mois='12' and rownum<2;