How do i delete duplicate rows from a table in Oracle?

Question

I didnt have a unique constraint in my table and found i had many duplicate rows in the table . How do i delete them .

Answer 1

lets say your table called table_1 has the following fields

(col_1, col_2,col_3)



step 1: as a safety measure back table_1 to table_backup by issuing

create table_backup as select * from table_1;

step 2: remove all non duplicate rows to another new table

create table_2 as select distinct col_1, col_2,col_3 from table_1;

step 3: delete all rows from table_1 by issuing

delete * from table_1;

commit;

step 4: bring back all rows from table_2 to table_1

insert into table_1 select * from table_2;



step 4: you should not have duplicated in table_1 now, you can delete table_2 and table_backup

drop table_2;

drop table_backup;

---------------------------------



another quick not safe way would be



delete from table_1 where col_1 not in

select distinct col_1from table_1;

commit;

//here col_1 will be the column you use to see there are duplicares

Answer 2

Hi. Avoid the Duplicate rows means we can use DISTINCT or Group by keyword.



Syntax:



1.Select Distinct (column-name) expression from table-name;

2.Select (columns) from table-name group by column-name;

Answer 3

difficult problem. search over yahoo or google. that will can help!