Kbase 47700: Como eliminar registros duplicados em Oracle
| Autor |
Eloi Rene Pscheidt - CAT |
| Acesso |
Público |
| Publicação |
11/03/2014 |
|
O procedimento a seguir poderá ser utilizado para eliminar de uma determinada tabela os registros duplicados com base em um índice único.
Obviamente este procedimento causará perda de dados. Utilizar somente se o registro inteiro estiver duplicado e se estas duplicidades estiverem realmente incorretas.
Neste exemplo, utilizaremos o seguinte cenário:
SQL> select * from teste;
C1 C2
---------- ----------
1 1
1 1
1 1
2 2
2 2
2 2
3 3
3 3
3 3
9 rows selected.
A coluna C1 não deveria apresentar valores duplicados.
Para validar se o registro inteiro está duplicado, utilize o seguinte comando:
SQL> select c1, c2, count(*) from teste group by c1, c2 having count(*) > 1;
C1 C2 COUNT(*)
---------- ---------- ----------
1 1 3
2 2 3
3 3 3
Esta consulta não deve retornar valores duplicados na coluna desejada (C1). Todos os campos da tabela devem ser referenciados nas cláusulas SELECT e GROUP BY.
Tentar criar um índice único nesta coluna causará o erro abaixo:
SQL> create unique index teste_unique on teste (c1);
create unique index teste_unique on teste (c1)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Para corrigir esta situação, renomeie a tabela para um nome temporário:
SQL> rename teste to teste_old;
Table renamed.
Crie a tabela novamente contendo apenas a estrutura da tabela antiga, sem os registros:
SQL> create table teste as select * from teste_old where 1=2;
Table created.
Crie o índice único antes de inserir os registros:
SQL> create unique index teste_unique on teste (c1);
Index created.
Execute o seguinte código PL/SQL:
SQL> begin
2 for c in (select c1, c2 from teste_old) loop
3 begin
4 insert into teste (c1, c2) values(c.c1, c.c2);
5 exception
6 when DUP_VAL_ON_INDEX then null;
7 end;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
O resultado será a tabela apenas com os registros corretos:
SQL> select * from teste;
C1 C2
---------- ----------
1 1
2 2
3 3
A tabela antiga poderá ser eliminada:
SQL> drop table teste_old;
Table dropped.
Confirme que o referido índice ficou ativo para a tabela:
SQL> select index_name, table_name, status, uniqueness from user_indexes where table_name = 'TESTE';
INDEX_NAME TABLE_NAME STATUS UNIQUENES
------------------------------ ------------------------------ -------- ---------
TESTE_UNIQUE TESTE VALID UNIQUE
Tags: unique constraint duplicate rows delete