0

I have following table:-

CREATE TABLE DATA 
(   
"FName" VARCHAR2(30 CHAR) NOT NULL ENABLE, 
"LName" VARCHAR2(30 CHAR) NOT NULL ENABLE, 
"BIRTH_DATE" DATE, 
"USERNAME" VARCHAR2(50 CHAR), 
"EXTRA_INFO" VARCHAR2(2000 CHAR), 
"START_DATE" DATE DEFAULT sysdate
);

This table has data as:

FName    LName   BIRTH_DATE   EXTRA_INFO   START_DATE

aaa      bbbb    01.01.1980   extra        01.01.2000
aaa      bbbb    01.01.1980   extra        01.01.2000     
aaa      bbbb    01.01.1980   extra        01.01.2000 
fname    lname   02.02.1970   something    02.02.1990

So I want to delete all the rows that have exact same entry multiple times. How can I delete them by SQL query?

masiboo
  • 4,537
  • 9
  • 75
  • 136

1 Answers1

0

Here's one option:

SQL> select * from data;

FNAME      LNAME      BIRTH_DATE EXTRA_INFO
---------- ---------- ---------- ----------
aaa        bbb        01.01.1980 extra
aaa        bbb        01.01.1980 extra
aaa        bbb        01.01.1980 extra
fna        ccx        02.02.1970 smthi

SQL> delete from data a
  2  where rowid > (select min(rowid)
  3                 from data b
  4                 where a.fname      = b.fname
  5                   and a.lname      = b.lname
  6                   and a.birth_date = b.birth_date
  7                   and a.extra_info = b.extra_info
  8                );

2 rows deleted.

SQL> select * From data;

FNAME      LNAME      BIRTH_DATE EXTRA_INFO
---------- ---------- ---------- ----------
aaa        bbb        01.01.1980 extra
fna        ccx        02.02.1970 smthi

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57