Remove repeated lines in all respects in Oracle [closed]

0

In a CADASTRO column I have two lines repeated in all respects:

NOME   ID    EMAIL           SEXO    
JOAO   1234  [email protected]  M
JOAO   1234  [email protected]  M

How to compare these two lines, check if all the columns are identical and delete one of them in order to result:

NOME   ID    EMAIL           SEXO    
JOAO   1234  [email protected]  M
    
asked by anonymous 04.05.2017 / 21:18

3 answers

2

Understanding that you want to delete duplicate records from the database, but keeping one (1) of them, I suggest using rowid .

ROWID is a pseudo-column that returns the address of a record, structurally you will not see it, but it's there. It uniquely identifies a row within a table.

In the script below we have:

  • A sub-query fetching all the minor rowid of each repeated grouping;

  • A delete command in all records that are not in the minimum collation done in the subquery;

Script:

delete from cadastro
where rowid not in
     (select min(rowid)
      from cadastro
group by nome, id, email, sexo);

With this, duplicates will be deleted leaving at least one of each.

Note: You should never store a rowid in your tables as a (unique) key value.     

05.05.2017 / 14:39
2

In sql server, the method below works. In oracle I think it looks similar:

select *
into #table
from(   select nome = 'JOAO', id=1234, email='[email protected]', sexo='m'
        union all
        select nome = 'JOAO', id=1234, email='[email protected]', sexo='m'
        union all
        select nome = 'manuel', id=1237, email='[email protected]', sexo='m') t

   ;WITH cte AS (
        SELECT  *, 
                row_number() OVER(PARTITION BY nome,id,email,sexo ORDER BY nome,id,email,sexo) AS rn
        FROM #table
    )
    DELETE cte WHERE rn > 1

select * 
from #table

drop table #table
    
05.05.2017 / 10:58
1

Use distinct .

Select Distinct * From Tabela;

See working in SQL Fiddle.

    
04.05.2017 / 21:20